Skip to content

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 StoreBackup MethodRPORTOStorage
SQL tenant DBs (prod)SQL Server Automated Backup1 hour2 hoursAzure Blob (RA-GRS)
SQL tenant DBs (non-prod)Weekly manual backup1 week4 hoursAzure Blob (LRS)
SQL admin DBDaily automated backup24 hours1 hourAzure Blob (RA-GRS)
Redis (prod)Redis persistence (RDB)1 hour30 minutesPremium P1 built-in
Redis (non-prod)No backup (ephemeral)N/AFlush and re-seedN/A
MongoDB (non-prod VM)mongodump weekly1 week2 hoursLocal + 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:

sql
-- 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 ​

bash
# 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) ​

sql
-- 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) ​

bash
# 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:

bash
# 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 ​

bash
# 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 ​

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

Backup Retention Policy ​

EnvironmentFull backup retentionLog backup retention
production30 days7 days
uat14 daysNot applicable
preprod7 daysNot applicable
stage / dev7 daysNot applicable

Retention is enforced by Azure Blob Storage lifecycle policies:

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

Internal Documentation — Microtec