Backup and point-in-time restore

Replicas protect against hardware and regional failures, but they faithfully copy every mistake. When an analyst ran an UPDATE without a WHERE clause on the StockLine price table in 2017, the change was on the mirror within a second. What saved the day was a log backup taken nine minutes earlier and Walt's practised restore to a point just before the mistake. Backups are still the answer to human error, corruption, ransomware and any other problem where the data itself is wrong. In Azure SQL Database and Managed Instance the service takes the backups; the DBA's job becomes choosing retention and redundancy, knowing how to restore, and keeping the few backups that the platform does not take. On SQL Server virtual machines, backups are again the customer's responsibility, with help from Azure.

Automated backups in SQL Database and Managed Instance

Both platform services back up every database automatically. They take full backups weekly, differential backups every 12 or 24 hours, and transaction log backups roughly every 5 to 10 minutes, depending on activity. The schedule is managed by the service and cannot be changed, but the result is the same as a well-run maintenance plan: a chain of backups from which the service can restore the database to any point within the retention period. Backups are compressed and encrypted when the database uses transparent data encryption, which is on by default.

Backup storage redundancy matters more than it looks. Locally redundant storage keeps three copies in one data centre; zone-redundant storage keeps them across availability zones; geo-redundant storage also copies them asynchronously to the paired region, which is what makes geo-restore possible when the primary region is lost. The choice also affects residency: geo-redundant backups for a database in Canada Central are stored in Canada East, which keeps them in Canada. Harrow & Finch's Azure Policy requires zone-redundant or geo-redundant backup storage for every production database, which is why Walt's test template from lesson 2 was refused.

Restoring to a point in time

A point-in-time restore always creates a new database; it never overwrites the existing one. That is a safety feature. After the 2017 incident, Walt restored a copy beside the live database, checked the old prices, and then copied the correct rows back, without taking StockLine offline. In Azure he would do the same: restore Pricing as Pricing_restore to 14:05, compare and repair, then drop the copy. If the whole database must be replaced, the usual way is to rename the damaged database, rename the restored copy to the original name, and keep the damaged one for a while for investigation. Restores can be started from the portal, PowerShell, the CLI or REST, and on Managed Instance also between instances in the same subscription and region.

az sql midb restore --resource-group rg-stockline-prod-cc --managed-instance hf-mi-stockline \
    --name Pricing --dest-name Pricing_restore --time "2026-11-19T20:05:00Z"

az sql db restore --resource-group rg-rewards-prod-cc --server hf-sql-rewards-prod-cc \
    --name finchrewards --dest-name finchrewards_restore --time "2026-11-19T20:05:00Z" --service-objective GP_Gen5_4

Times are given in UTC, which catches people out. A mistake noticed at 14:07 Winnipeg time in November, when Central Standard Time is six hours behind UTC, means a restore point of 20:05 UTC. Walt's runbook includes a conversion table for both standard and daylight time, because a restore to the wrong hour either misses the data or brings the mistake back.

Deleted databases and geo-restore

If a database is dropped by mistake, it can be restored within its retention period from the list of deleted databases on its server or instance. Dropping the logical server or the managed instance itself removes its backups, which is why production resource groups at Harrow & Finch carry a delete lock. Geo-restore uses the geo-redundant copies of backups in the paired region to create a database on any server or instance, even when the primary region is unavailable. The geo-replicated backups lag the primary by up to about an hour, so geo-restore's RPO is roughly an hour and its RTO is the time to restore, which can be several hours for large databases. For tier 3 databases such as SalesHistory, that is the whole disaster-recovery plan.

Backups you take yourself

Managed Instance allows customer-initiated backups with the COPY_ONLY option to Azure Blob Storage, which do not break the automated backup chain. Walt will take one each month of StockLine for the auditors, who want a copy they control and can keep outside the service. Managed Instance will not take a copy-only backup of a database protected by transparent data encryption with a service-managed key, because nobody outside the service could ever decrypt it; the database must use a customer-managed key in Azure Key Vault, which the restoring server can also be given access to. Walt's design uses a customer-managed key for that reason. Azure SQL Database does not allow native backups at all; the equivalents are database copies and BACPAC exports.

-- On the managed instance: a credential for the storage container, using a managed identity.
CREATE CREDENTIAL [https://hfbackupaudit.blob.core.windows.net/stockline]
    WITH IDENTITY = 'Managed Identity';

BACKUP DATABASE StockLine
    TO URL = 'https://hfbackupaudit.blob.core.windows.net/stockline/StockLine_2026-11-30.bak'
    WITH COPY_ONLY, COMPRESSION, CHECKSUM, STATS = 10;

-- Restoring a native backup into a managed instance (for example, from SQL Server):
RESTORE DATABASE StockLine_audit_check
    FROM URL = 'https://hfbackupaudit.blob.core.windows.net/stockline/StockLine_2026-11-30.bak';

Backups on SQL Server virtual machines

On a SQL Server virtual machine, Walt is back in charge of backups, with the full T-SQL BACKUP and RESTORE commands and any schedule he wants. Azure helps in three ways. The SQL IaaS Agent extension can run automated backups to a storage account, with a retention period and optional encryption. Azure Backup can protect SQL Server databases inside virtual machines with application-consistent backups, log backups as often as every 15 minutes and central reporting. And SQL Server's own backup to URL writes directly to Azure Blob Storage, with a shared access signature credential or, on recent versions in Azure, a managed identity. The warehouse system uses Azure Backup, because the vendor's support team is familiar with it and because Walt can see all its backup jobs in one place.

-- Backup to URL from the warehouse VM with a shared access signature credential.
CREATE CREDENTIAL [https://hfbackupwms.blob.core.windows.net/wms]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<SAS token without the leading question mark>';

BACKUP LOG WMS TO URL = 'https://hfbackupwms.blob.core.windows.net/wms/WMS_log_202611191400.trn'
    WITH COMPRESSION, CHECKSUM;

RESTORE DATABASE WMS_check FROM URL = 'https://hfbackupwms.blob.core.windows.net/wms/WMS_full_20261115.bak'
    WITH MOVE 'WMS' TO 'F:\Data\WMS_check.mdf', MOVE 'WMS_log' TO 'G:\Log\WMS_check.ldf', NORECOVERY;
RESTORE LOG WMS_check FROM URL = 'https://hfbackupwms.blob.core.windows.net/wms/WMS_log_202611191400.trn'
    WITH STOPAT = '2026-11-19T14:05:00', RECOVERY;

Backup storage cost

Backup storage is not free without limit. For Azure SQL Database in the vCore model, backup storage up to the database's maximum data size is included, and anything above that is billed per GB per month; Managed Instance includes backup storage up to the instance's reserved storage. Longer retention, high change rates and geo-redundant storage all raise the amount and the price. Walt's rule is to set point-in-time retention to what the business needs, not to the maximum, and to use long-term retention for anything older than a few weeks.

Choosing a backup strategy

The study guide asks a DBA to recommend a backup and restore strategy, not just to know the commands. Walt's recommendation for each database answers five questions. How far back must a point-in-time restore reach? That sets the retention, usually 7 to 35 days. Where must the backups survive? That sets the redundancy: zone-redundant for databases whose disaster recovery is a failover group, geo-redundant where geo-restore is the disaster-recovery plan. How old a copy might anyone ever ask for? That sets long-term retention, the subject of the next lesson. Does anyone outside the service need a copy? That decides whether copy-only backups or exports are needed. And how quickly must a restore finish? That decides whether the restore can be the recovery plan or only a tool for errors.

The answers for the Harrow & Finch estate follow the recovery tiers from lesson 4. Tier 1 and tier 2 databases have 14 days of point-in-time retention, because some pricing and loyalty errors are found only at the end of a two-week promotion, and zone-redundant backup storage, because their regional disaster recovery is a failover group. SalesHistory keeps 7 days with geo-redundant storage, because geo-restore is its whole plan. The warehouse virtual machines keep 30 days in Azure Backup, set by the vendor's support policy. Writing these choices down, with the reason for each, matters as much as configuring them: a reviewer who sees 35 days of retention on a test database without a reason will rightly ask why the company is paying for it.

One more habit completes the strategy. Every backup policy is only as good as the last restore that proved it, so the monthly restore test in lesson 11 rotates through all the databases, and its measured times feed back into the recovery estimates in the runbook.

Restores on Managed Instance also run in the background and appear in the instance's list of operations, so a long restore can be watched with the same commands used for scaling in lesson 2, and cancelled if it was started with the wrong time.