13.3 Backup and Restore Strategy with Native Tools

Recovery Models and the Log Chain

The recovery model is the foundation of any backup strategy because it controls how the transaction log is managed and whether point-in-time recovery is possible.

• SIMPLE: the log is truncated at every checkpoint. No log backups are possible, no point-in-time recovery is possible, and the log does not grow unbounded. Suitable for dev/test and read-only databases where data loss back to the last full/differential backup is acceptable. RPO equals the backup interval.
• FULL: the log is preserved until a log backup truncates it. Log backups form a log chain; together with a full backup they support point-in-time recovery to any moment between log backups. This is the model the exam assumes for any production OLTP database. The cost is operational: you must take log backups regularly or the log grows until the disk fills.
• BULK_LOGGED: like FULL but bulk operations (bulk insert, index rebuilds, SELECT INTO) are minimally logged, reducing log space during those operations. You can switch between FULL and BULK_LOGGED before and after a bulk operation, but the log chain continues - if a bulk-logged operation is caught in a damaged log segment, you cannot recover past it. The exam treats BULK_LOGGED as a tactical optimization, not a steady-state model.

A critical trap: switching from FULL to SIMPLE breaks the log chain. You cannot take log backups in SIMPLE, and switching back to FULL starts a new chain - a full backup is required to re-establish a recoverable baseline. An exam scenario that switches to SIMPLE to free log space and then expects PITR is wrong.

Backup Types

The three core backup types form a layered strategy:

A full backup establishes a baseline. A differential backup captures changes since the last full and grows over time; restore is faster than replaying every log backup since the full. A log backup captures the transactional delta since the prior log backup and is the basis of point-in-time recovery. COPY_ONLY backups are special: a copy-only full backup does not reset the differential base (so the next differential still covers changes since the last regular full), and a copy-only log backup does not truncate the log or break the log chain. The exam tests copy-only as the right answer for ad-hoc backups taken for offsite copies or migration without disturbing the regular backup chain.

File and filegroup backups allow backing up only part of a very large database, with corresponding filegroup log backups; they enable piecemeal restore (restore the primary filegroup first, bring the database online, then restore other filegroups while the database is partially available). Piecemeal restore is an Enterprise feature relevant to SQL Server on VMs and SQL MI; not a concern for Azure SQL Database.

Azure SQL Database Automated Backups

Azure SQL Database takes automated backups for every database - you do not configure or schedule them. The schedule and retention are:

• Full backups weekly (the first backup is a full, taken immediately after database creation).
• Differential backups generally every 12-24 hours.
• Log backups approximately every 5-10 minutes for Hyperscale and every 10-15 minutes for other tiers, but the platform documents log backups as occurring "approximately every 12 hours" in some materials - verify current behavior against Microsoft Learn for the specific tier.
• Point-in-time restore (PITR) is available across a configurable retention window. For Azure SQL Database and SQL MI the default is 7 days, configurable between 1 and 35 days (Basic databases are configurable only between 1 and 7 days). Shorten it for dev/test, lengthen it for compliance, and use long-term retention for anything beyond 35 days. Always confirm the current documented range rather than memorizinga single number.
• Geo-redundant backups store backups in the paired region by default, enabling geo-restore with a longer RTO than PITR.

Backups are storage-backed: on Hyperscale, backups are snapshot-based and nearly instantaneous regardless of database size, which is why Hyperscale restore is minutes rather than hours for multi-TB databases.

SQL Managed Instance and SQL Server on VMs

SQL Managed Instance also takes automated backups with the same PITR window and adds copy-only user backups to Azure Blob Storage (URL) via T-SQL BACKUP DATABASE ... TO URL WITH COPY_ONLY. User-initiated regular (non-copy-only) backups on SQL MI can break the automated backup chain, so the platform restricts non-copy-only backups; the exam answer for SQL MI manual backups is to use WITH COPY_ONLY and target a storage URL with a SAS credential.

SQL Server on Azure VMs does not take automated backups by default. Two paths:

• Manual backups via T-SQL BACKUP DATABASE to Azure Blob Storage (URL), managed disk, or local storage; you own the schedule, retention, and verification.
• SQL IaaS Agent extension automated backup, which schedules full and log backups to Azure Storage with configurable retention and encryption. This is the recommended managed path for VMs and a frequent exam answer.

Backup Destination and Encryption

On Azure, the recommended backup destination is Azure Blob Storage via a URL endpoint, authenticated with a SAS token stored as a SQL Server credential. The blob type is a page blob for block-based backups (SQL Server) and block blob for managed-instance copy-only backups. Geo-redundant storage (GRS or RA-GRS) gives DR copies for free; locally redundant storage is cheaper. Backup encryption uses a certificate or asymmetric key protected by the database master key, or transparent data encryption (TDE) for Azure SQL Database/MI backups which are encrypted automatically when TDE is enabled.

Native Restore Syntax and Sequence

Restoring from native backups follows a strict sequence when recovering to a point in time:

1. Restore the most recent full backup with WITH NORECOVERY.
2. Restore the most recent differential backup (if any) with WITH NORECOVERY.
3. Restore each subsequent log backup in order, with WITH NORECOVERY for all but the last.
4. Restore the final log backup with STOPAT and WITH RECOVERY to land at the desired point in time.

Key clauses: WITH RECOVERY brings the database online and cannot be followed by more restores; WITH NORECOVERY keeps the database restoring so additional logs can be applied; WITH REPLACE overwrites an existing database of the same name (use carefully - it bypasses safety checks); WITH MOVE relocates data and log files to new paths when restoring to a different server or when original paths are unavailable.

RESTORE DATABASE [SalesDB] FROM URL = 'https://storage.blob.core.windows.net/backups/SalesDB_full.bak'
  WITH NORECOVERY, MOVE 'SalesDB' TO '/var/opt/mssql/data/SalesDB.mdf',
  MOVE 'SalesDB_log' TO '/var/opt/mssql/data/SalesDB_log.ldf';
RESTORE DATABASE [SalesDB] FROM URL = '...SalesDB_diff.bak' WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM URL = '...SalesDB_log1.trn' WITH NORECOVERY;
RESTORE LOG [SalesDB] FROM URL = '...SalesDB_log2.trn'
  WITH RECOVERY, STOPAT = '2026-08-03T14:30:00';

Checksums and Compression

Two options appear on most production backups. CHECKSUM (or WITH CHECKSUM) makes the backup verify page checksums as it reads and writes a backup checksum, allowing restore to detect corruption - strongly recommended and a frequent exam answer. BACKUP ... WITH NO_CHECKSUM skips verification for speed. Backup compression (WITH COMPRESSION) shrinks backup size at the cost of CPU, dramatically reducing storage cost and backup/restore time for compressible data; on Azure SQL it is on by default. The tradeoff is the standard one: trade CPU for I/O and storage savings.

Recommending a Backup Strategy

Translate RPO and retention targets into a concrete schedule:

• RPO = 15 minutes implies log backups every 15 minutes (or less).
• Retention = 35 days requires keeping 35 days of full, differential, and log backups; for SQL on VMs use a weekly full + daily differential + 15-minute logs pattern; for Azure SQL Database/MI configure the 35-day PITR window.
• Storage: GRS or RA-GRS for DR; LRS for dev/test.
• Encryption: TDE for Azure SQL; certificate-protected backups for SQL on VMs.
• Verification: run RESTORE VERIFYONLY on a sample, and periodically restore to a test environment to confirm the chain is usable.