Backup and Restore

Microtec uses a multi-tier backup strategy covering SQL Server tenant databases, the admin database, Redis, and MongoDB (used for non-production document storage). Each backup tier has defined RPO (Recovery Point Objective) and RTO (Recovery Time Objective) targets.

---

Backup Overview

Data Store	Backup Method	RPO	RTO	Storage
SQL tenant DBs (prod)	SQL Server Automated Backup	1 hour	2 hours	Azure Blob (RA-GRS)
SQL tenant DBs (non-prod)	Weekly manual backup	1 week	4 hours	Azure Blob (LRS)
SQL admin DB	Daily automated backup	24 hours	1 hour	Azure Blob (RA-GRS)
Redis (prod)	Redis persistence (RDB)	1 hour	30 minutes	Premium P1 built-in
Redis (non-prod)	No backup (ephemeral)	N/A	Flush and re-seed	N/A
MongoDB (non-prod VM)	mongodump weekly	1 week	2 hours	Local + Azure Blob

---

SQL Server Backup Strategy

Production — Automated SQL Backup

SQL Server Backup to URL is configured to write to Azure Blob Storage with geo-redundancy:

-- Full backup (runs daily at 02:00 GST via SQL Agent job)
BACKUP DATABASE [ACME_Corp_DB]
TO URL = 'https://micerpbesqbkprod.blob.core.windows.net/backups/ACME_Corp_DB/full_20240115.bak'
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10,
    CREDENTIAL = 'AzureBlobStorageCredential';

-- Differential backup (runs every 4 hours)
BACKUP DATABASE [ACME_Corp_DB]
TO URL = 'https://micerpbesqbkprod.blob.core.windows.net/backups/ACME_Corp_DB/diff_20240115_0600.bak'
WITH
    DIFFERENTIAL,
    COMPRESSION,
    CHECKSUM;

-- Transaction log backup (runs every 15 minutes)
BACKUP LOG [ACME_Corp_DB]
TO URL = 'https://micerpbesqbkprod.blob.core.windows.net/backups/ACME_Corp_DB/log_20240115_0615.trn'
WITH COMPRESSION, CHECKSUM;

Non-Production — Manual Weekly Backup

# Run on SQL VM via SSH
ssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95

# List all tenant databases
sqlcmd -S localhost -Q "SELECT name FROM sys.databases WHERE name LIKE '%_DB' ORDER BY name;"

# Backup all tenant databases (PowerShell script on VM)
/opt/mssql-tools/bin/sqlcmd -S localhost \
  -Q "EXEC sp_MSforeachdb 'IF ''?'' LIKE ''%_DB'' 
      BEGIN
        BACKUP DATABASE [?] 
        TO DISK = N''/var/opt/mssql/backups/?_$(date +%Y%m%d).bak''
        WITH COMPRESSION, CHECKSUM
      END'"

---

SQL Restore Procedure

Production Tenant Restore (Point-in-Time)

-- Step 1: Restore full backup
RESTORE DATABASE [ACME_Corp_DB_Restore]
FROM URL = 'https://micerpbesqbkprod.blob.core.windows.net/backups/ACME_Corp_DB/full_20240115.bak'
WITH
    MOVE 'ACME_Corp_DB' TO '/var/opt/mssql/data/ACME_Corp_DB_Restore.mdf',
    MOVE 'ACME_Corp_DB_log' TO '/var/opt/mssql/log/ACME_Corp_DB_Restore.ldf',
    NORECOVERY,
    CREDENTIAL = 'AzureBlobStorageCredential';

-- Step 2: Apply differential backup
RESTORE DATABASE [ACME_Corp_DB_Restore]
FROM URL = '...diff_20240115_0600.bak'
WITH NORECOVERY, CREDENTIAL = 'AzureBlobStorageCredential';

-- Step 3: Apply log backups up to target time
RESTORE LOG [ACME_Corp_DB_Restore]
FROM URL = '...log_20240115_0615.trn'
WITH
    STOPAT = '2024-01-15T06:00:00',
    RECOVERY,
    CREDENTIAL = 'AzureBlobStorageCredential';

Restore to Non-Production (for UAT data refresh)

# SSH into SQL VM
ssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95

# Restore production backup to uat_ prefixed database
python3 /opt/scripts/restore-tenant.py \
  --source "ACME_Corp_DB" \
  --backup-file "/var/opt/mssql/backups/ACME_Corp_DB_20240115.bak" \
  --target "ACME_Corp_DB_UAT" \
  --anonymise true    # Runs anonymisation script after restore

Always Anonymise Before Restoring to Non-Production

Production backups contain real customer data. Before restoring to any non-production environment, the anonymisation script (devops/scripts/Anonymize-TenantData.ps1) must run to scramble PII fields. Never skip this step.

---

Backup Verification

Backups are verified weekly by the automated restore-and-verify job:

# Weekly backup verification job (runs in Azure Automation)
# 1. Restore latest full backup to a temporary database
# 2. Run consistency check
# 3. Drop the temporary database
# 4. Report success/failure to the monitoring dashboard

DBCC CHECKDB([ACME_Corp_DB_VerifyTest]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Backup Testing Is Mandatory

A backup that has never been tested is not a backup — it is a hope. The restore-and-verify job runs every Sunday at 03:00 GST and sends results to devops@microtec.com.sa. Failures create a P2 alert.

---

MongoDB Backup (Non-Production VM)

MongoDB runs on a VM for non-production environments (dev, stage) on ports 27017–27020 (replica set). Production uses Azure Cosmos DB (managed service with built-in backup).

mongodump Backup

# Runs weekly via cron on MongoDB VM
mongodump \
  --host "localhost:27017" \
  --username admin \
  --password "${MONGO_ADMIN_PWD}" \
  --authenticationDatabase admin \
  --out /var/backups/mongodb/$(date +%Y%m%d) \
  --gzip

# Compress and upload to Azure Blob
tar -czf /tmp/mongodb-$(date +%Y%m%d).tar.gz /var/backups/mongodb/$(date +%Y%m%d)
az storage blob upload \
  --account-name micerpbedevsa \
  --container-name mongodb-backups \
  --name "mongodb-$(date +%Y%m%d).tar.gz" \
  --file /tmp/mongodb-$(date +%Y%m%d).tar.gz \
  --auth-mode login

mongorestore

mongorestore \
  --host "localhost:27017" \
  --username admin \
  --password "${MONGO_ADMIN_PWD}" \
  --authenticationDatabase admin \
  --gzip \
  --drop \
  /var/backups/mongodb/20240115/

---

Backup Retention Policy

Environment	Full backup retention	Log backup retention
production	30 days	7 days
uat	14 days	Not applicable
preprod	7 days	Not applicable
stage / dev	7 days	Not applicable

Retention is enforced by Azure Blob Storage lifecycle policies:

{
  "rules": [{
    "name": "delete-old-backups",
    "type": "Lifecycle",
    "definition": {
      "filters": { "blobTypes": ["blockBlob"], "prefixMatch": ["backups/"] },
      "actions": {
        "baseBlob": { "delete": { "daysAfterModificationGreaterThan": 30 } }
      }
    }
  }]
}