Plan and perform backup and restore of a database
Unlock the complete study guide + 25 practice exams totaling 1,500 questions.
Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.
14-day refund guarantee on one-time purchases and your first subscription charge.
Included in this chapter:
- Customer-run and service-managed backups
- Recovery models decide which restores are possible
- Full, differential, and log backups form one chain
- Copy-only backups stay outside the sequence
- System databases need backups of their own
- Taking a native backup with SSMS or T-SQL
- Who may run BACKUP and RESTORE
- Backup checksums detect damage, they don't repair it
- Backup compression trades CPU for less I/O
- Backups on availability group replicas
- Azure Backup for SQL Server on Azure VMs
- Protecting backup recovery points from deletion
- Restore states: RECOVERY, NORECOVERY, and STANDBY
- Choosing the restore source in SSMS and T-SQL
- Restoring a copy beside the original or over it
- Version and encryption limits on where a backup restores
- Tail-log backups before restoring over a database
- Restoring to a point in time with STOPAT
- Marked transactions for related databases
- Piecemeal restore of a multi-filegroup database
- Page restore for isolated damaged pages
- Keeping CDC and replication settings at recovery
- Reverting to a database snapshot
- T-SQL snapshot backups in SQL Server 2022 and later
- File-snapshot backups for database files in Azure
- Credentials for BACKUP TO URL
- Blob size limits, stripes, and overwriting
- Azure SQL Database automated backups
- Backup storage redundancy and geo-restore
- Point-in-time restore creates a new database
- What PITR backup storage costs
- Long-term retention policies
- LTR backups outlive their database and server
- Making long-term backups immutable
- LTR restores across network security perimeters
- Managed Instance native backups are copy-only full backups
- Managed Instance native restore runs asynchronously
- Recommend a database backup and restore strategy
Customer-run versus service-managed backups
| Property | Native SQL Server backups | Azure Backup for SQL Server on Azure VMs | Azure SQL Database automated backups | Managed Instance automated backups |
|---|---|---|---|---|
| Operating model | Customer-run | Customer-run | Service-managed | Service-managed |
| Who sets the schedule | You, with BACKUP statements or scheduled jobs | Your Azure Backup policy | The service; you can't change or disable it | The service, in a weekly full-backup window it sets |
| Backup types | Full, differential, log, copy-only, and snapshot backups | Full, differential, and log; or instance snapshots plus log backups | Weekly full, differential every 12 or 24 hours, log about every 10 minutes (Hyperscale uses storage snapshots) | Weekly full, differential every 12 or 24 hours, log about every 10 minutes |
| Where the backups live | Disk, tape, or URL (Azure Blob Storage or S3-compatible storage) | Recovery Services vault | Microsoft-managed storage that you can't access directly | Microsoft-managed storage; your customer-run COPY_ONLY backups go to your own Blob Storage |
| How you restore | RESTORE in T-SQL or SSMS | Restore from a vault recovery point | PITR, geo-restore, or LTR restore through the portal, CLI, PowerShell, or REST API (not T-SQL) | PITR, geo-restore, or LTR restore; RESTORE ... FROM URL for your own customer-run COPY_ONLY backups |
| Retention beyond the recent window | You keep the backup files | Retention set in the backup policy | LTR, up to 10 years | LTR, up to 10 years |
| Deletion protection | Your storage controls (SQL Server 2025 supports immutable Blob containers) | Soft delete and immutable backups | Immutable LTR backups | LTR backups stay deletable; keep customer-run COPY_ONLY backups on immutable storage instead |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview
- https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/automated-backups-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server
- https://learn.microsoft.com/en-us/azure/backup/backup-azure-sql-database
- https://learn.microsoft.com/en-us/azure/backup/backup-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-server
- https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server
- https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/media-sets-media-families-and-backup-sets-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/possible-media-errors-during-backup-and-restore-sql-server
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
- https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/database/backup-immutability
- https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-devices-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-of-related-databases-that-contain-marked-transaction
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/piecemeal-restores-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/databases/revert-a-database-to-a-database-snapshot
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/file-snapshot-backups-for-database-files-in-azure
- https://learn.microsoft.com/en-us/azure/storage/common/shared-key-authorization-prevent
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart
- https://learn.microsoft.com/en-us/powershell/module/az.sql/restore-azsqldatabase
- https://learn.microsoft.com/en-us/cli/azure/sql/db
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore
- https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackuplongtermretentionpolicy
- https://learn.microsoft.com/en-us/cli/azure/sql/db/ltr-backup
- https://learn.microsoft.com/en-us/azure/private-link/network-security-perimeter-concepts