Domain 5 of 5 · Chapter 2 of 3

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

PropertyNative SQL Server backupsAzure Backup for SQL Server on Azure VMsAzure SQL Database automated backupsManaged Instance automated backups
Operating modelCustomer-runCustomer-runService-managedService-managed
Who sets the scheduleYou, with BACKUP statements or scheduled jobsYour Azure Backup policyThe service; you can't change or disable itThe service, in a weekly full-backup window it sets
Backup typesFull, differential, log, copy-only, and snapshot backupsFull, differential, and log; or instance snapshots plus log backupsWeekly 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 liveDisk, tape, or URL (Azure Blob Storage or S3-compatible storage)Recovery Services vaultMicrosoft-managed storage that you can't access directlyMicrosoft-managed storage; your customer-run COPY_ONLY backups go to your own Blob Storage
How you restoreRESTORE in T-SQL or SSMSRestore from a vault recovery pointPITR, 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 windowYou keep the backup filesRetention set in the backup policyLTR, up to 10 yearsLTR, up to 10 years
Deletion protectionYour storage controls (SQL Server 2025 supports immutable Blob containers)Soft delete and immutable backupsImmutable LTR backupsLTR backups stay deletable; keep customer-run COPY_ONLY backups on immutable storage instead

Decision tree

Service-managedbackups?Restore yourcustomer-runbackup chainPoint insideshort-termretention?LTR restore(a retainedfull backup)Target inanother region?Geo-restore(latest geo-replicated backup)PITR to anew databaseNoYesNoYesYesNo

Cheat sheet

  • Unrestricted point-in-time protection requires full recovery and a usable chain of log backups
  • A differential restore needs its full base, but not necessarily later log backups
  • Instance recovery needs system database backups as well as user database backups
  • Azure Backup offers SQL-aware streaming or instance snapshots for SQL Server on Azure VMs
  • Backup retention needs deletion protection when recoverable SQL copies must survive an attack
  • A copy-only full backup captures an ad hoc copy without resetting the differential base
  • Native backups require SQL permission and storage access for the SQL Server service
  • Native backup tools distinguish the backup type from the number of destination files
  • Backup checksums detect damage but do not repair database pages
  • Backup compression reduces transfer volume at the cost of additional CPU work
  • Azure Backup SQL protection requires its workload service identities to have SQL access
  • Availability-group backup preferences work only when backup jobs evaluate the preferred replica
  • T-SQL storage-snapshot backups require captured database files as well as metadata
  • SSMS can build a restore plan from backup history or explicitly supplied media
  • Restoring to new storage requires logical-file mappings rather than just a new database name
  • Native restore authority differs from database role membership and active connection access
  • NORECOVERY, RECOVERY, and STANDBY provide different access and continuation states
  • A portable backup still needs a compatible engine and its encryption material
  • Piecemeal restore recovers critical filegroups before the rest of a SQL Server database
  • Page restore repairs isolated data-page damage without restoring the whole database
  • Restore metadata preservation requires the matching KEEP option at recovery
  • Database snapshot reversion undoes local changes without providing independent media recovery
  • STOPAT recovers a SQL Server database to a time covered by its log backups
  • A tail-log backup captures the final unbacked log before restoring over a database
  • Marked transactions provide a common recovery boundary for related databases
  • Azure SQL Database automated backup cadence and retention define its recent recovery window
  • Azure SQL Database PITR creates a separate database rather than overwriting the live one
  • Managed Instance PITR has broader instance placement but still requires a same-region target
  • PITR backup storage depends on retained recovery chains and write activity rather than live data size alone
  • Long-term retention preserves selected full backups for as long as ten years
  • An LTR policy governs future backups of its database while it is primary
  • Existing LTR backups can survive deletion of their original database host
  • A long retention policy does not by itself make Azure SQL backups immutable
  • Cross-perimeter LTR restore access requires directional admission rules
  • SAS-authenticated Azure Blob backups bind a container credential to an individual blob URL
  • SQL Server Azure Blob backups require explicit planning for stripe size and blob reuse
  • Managed Instance user-initiated backups are copy-only full backups to Azure Blob Storage
  • Managed Instance native restore uses Azure Blob URLs without SQL Server restore options
  • Azure SQL backup redundancy determines whether backups can support regional recovery
  • SQL Server file-snapshot backups depend on snapshots of database files already stored as Azure blobs

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview
  2. https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
  3. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  4. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/automated-backups-overview
  5. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore
  6. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server
  7. https://learn.microsoft.com/en-us/azure/backup/backup-azure-sql-database
  8. https://learn.microsoft.com/en-us/azure/backup/backup-overview
  9. https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups
  10. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
  11. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server
  12. https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server
  13. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model
  14. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/differential-backups-sql-server
  15. https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql
  16. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server
  17. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server
  18. https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
  19. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/media-sets-media-families-and-backup-sets-sql-server
  20. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms
  21. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
  22. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/possible-media-errors-during-backup-and-restore-sql-server
  23. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
  24. https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql
  25. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server
  26. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups
  27. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server
  28. https://learn.microsoft.com/en-us/azure/azure-sql/database/backup-immutability
  29. https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview
  30. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-devices-sql-server
  31. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
  32. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server
  33. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-of-related-databases-that-contain-marked-transaction
  34. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/piecemeal-restores-sql-server
  35. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server
  36. https://learn.microsoft.com/en-us/sql/relational-databases/databases/revert-a-database-to-a-database-snapshot
  37. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup
  38. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/file-snapshot-backups-for-database-files-in-azure
  39. https://learn.microsoft.com/en-us/azure/storage/common/shared-key-authorization-prevent
  40. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage
  41. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart
  42. https://learn.microsoft.com/en-us/powershell/module/az.sql/restore-azsqldatabase
  43. https://learn.microsoft.com/en-us/cli/azure/sql/db
  44. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore
  45. https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackuplongtermretentionpolicy
  46. https://learn.microsoft.com/en-us/cli/azure/sql/db/ltr-backup
  47. https://learn.microsoft.com/en-us/azure/private-link/network-security-perimeter-concepts