14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage

Why LTR Matters for the DP-300 Exam

Domain 5 weights HA/DR at 20-25 percent, and a large slice of that is backup retention and recovery to cloud storage. The exam expects you to distinguish what the platform does for you (automated backups, point-in-time restore, built-in LTR on Azure SQL Database) from what you must do yourself (T-SQL backups on SQL Managed Instance and SQL Server on VMs, lifecycle of those backups across storage tiers). It also expects you to choose the right storage redundancy and tier for a stated RPO/RTO and cost target, and to know exactly which T-SQL commands work on which Azure SQL deployment option.

Azure SQL Database Long-Term Retention (LTR)

Azure SQL Database takes automated backups - full, differential, and log backups - automatically and at no extra cost; the default retention is 7 days on every tier, configurable from 1 to 35 days (Basic databases are capped at 7), with point-in-time restore available within that window. Long-term retention (LTR) extends this to up to 10 years by keeping the automated full backups in Azure Blob storage beyond the short-term retention window.

LTR is configured per database with three retention knobs that map to backup frequency:

| LTR policy | Backup promoted | Typical retention use case | |---|---|---|---| | Weekly | One full backup per week | Rolling weekly compliance window (e.g. 12 weeks) | | Monthly | First full backup of the month | Monthly archives (e.g. 12 months) | | Yearly | First full backup of the year | Long regulatory retention (e.g. 7 or 10 years) |

The first full backup in each period is copied to LTR storage; subsequent LTR copies happen at the configured cadence until the policy's week-of-year slot is filled. You can configure LTR through the Azure portal (database -> Backups -> Long-term retention -> Configure policies), PowerShell (Set-AzSqlDatabaseBackupLongTermRetentionPolicy / New-AzSqlDatabaseLongTermRetentionPolicy), or the Azure CLI (az sql db ltr-policy set). A key point: LTR is a per-database setting, not a server-wide default.

Restoring from LTR creates a new database on the same logical server (or a different server in a paired region, depending on the source storage). It never overwrites an existing database, and it does not roll forward log backups beyond the chosen full backup's point in time - the LTR restore lands you at the moment that weekly/monthly/yearly full was taken. The portal exposes a restore wizard that lists available LTR backups by timestamp; PowerShell uses Restore-AzSqlDatabase -FromLongTermRetentionBackup.

SQL Managed Instance LTR: Copy-Only Backups to URL

SQL Managed Instance also receives automated short-term backups, but its LTR story is different: the platform does not promote automated backups into a multi-year LTR policy on MI. Instead, you take user-initiated copy-only backups to Azure Blob storage on your own schedule and manage their lifecycle. The canonical command:

BACKUP DATABASE [Sales] TO URL = 'https://mystorage.blob.core.windows.net/backups/sales_full.bak' WITH COPY_ONLY, COMPRESSION, STATS = 5;

The WITH COPY_ONLY clause is mandatory on MI so the user backup does not disrupt the platform's automated backup chain. You then apply blob-tier lifecycle rules (move to cool after 30 days, archive after 180 days, delete after 10 years) on the storage account to achieve the long-term posture.

T-SQL Backup and Restore to Cloud Storage

T-SQL backup to URL is supported on SQL Managed Instance (copy-only) and SQL Server on Azure VMs, and is not supported on Azure SQL Database (where backups are automated and BACKUP DATABASE is blocked). The flow uses a credential backed by a storage account shared access signature (SAS) token:

CREATE CREDENTIAL [https://mystorage.blob.core.windows.net/backups]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS-token-with-rwld-list-permissions>';

BACKUP DATABASE [Sales] TO URL = 'https://mystorage.blob.core.windows.net/backups/sales_full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

BACKUP LOG [Sales] TO URL = 'https://mystorage.blob.core.windows.net/backups/sales_log.trn'
WITH COMPRESSION, CHECKSUM;

Restore reads back from the same URL and typically needs WITH MOVE to relocate the data and log files onto the target instance's storage paths:

RESTORE DATABASE [Sales] FROM URL = 'https://mystorage.blob.core.windows.net/backups/sales_full.bak'
WITH MOVE 'Sales' TO '/var/opt/mssql/data/sales.mdf',
     MOVE 'Sales_log' TO '/var/opt/mssql/data/sales_log.ldf',
     REPLACE, STATS = 5;

A common exam trap: forgetting to create the credential, or creating it with IDENTITY = 'SHARED ACCESS SIGNATURE' but a SAS token that lacks Read, List, Write, Delete permissions, causes the backup or restore to fail. Another trap: the credential name must exactly match the blob container URL (including the trailing path) you target.

SQL Server Managed Backup to Microsoft Azure (VMs)

For SQL Server on Azure VMs, SQL Server Managed Backup to Microsoft Azure is the platform-recommended automation layer that schedules full, differential, and log backups to Azure Blob storage based on the workload's log growth, rather than a fixed schedule. It is configured at the instance or database level with managed_backup.sp_backup_config_basic and uses a storage account URL plus a credential. It encrypts backups with a certificate or asymmetric key if configured, and it transparently handles log backup frequency to meet your RPO.

Storage Account Types, Tiers, and Encryption

Choosing the storage account underpins durability and cost:

• Redundancy: RA-GRS (read-access geo-redundant storage) or GZRS (geo-zone-redundant) replicates backups to a paired region and gives read access to the secondary - the recommended option for backups that must survive a regional outage. LRS is cheapest but only protects against hardware failure within a single datacenter.
• Access tiers: Hot for backups you expect to restore from within days (highest storage cost, no retrieval fee), Cool for the 30-180 day window (lower storage cost, small retrieval fee), Archive for multi-year retention (lowest storage cost, hours of retrieval latency and a higher per-GB retrieval fee). Lifecycle management rules move blobs between tiers automatically.
• Encryption: backups written to Blob storage are encrypted at rest with storage account encryption (Microsoft-managed keys by default, customer-managed keys supported). For defense in depth, T-SQL BACKUP ... WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = ...) encrypts the backup file itself before upload; you must back up the certificate and private key separately, or restores become impossible.

Restore from Cloud Storage and Limitations

Restore from cloud storage uses the same SAS-backed credential, and on SQL Server VMs supports restore from a striped backup set (multiple backup files across multiple URLs for parallelism) and file-snapshot backups for databases using Azure Blob storage for data files. On Azure SQL Database you cannot restore from a user-created backup file - you can only use the platform's point-in-time restore or LTR restore. On SQL MI you can restore from a backup file in Azure Blob (URL), which is the standard migration path from on-premises SQL Server. Remember the rule: a restore always produces a new database; you cannot restore over the online source database.