SQL Server Backup Best Practices: A 2026 Checklist

A SQL Server backup strategy is only as good as the last restore you tested. Plenty of teams discover during an outage that their log backups stopped weeks ago, that the backup files sit on the same storage array that just failed, or that nobody remembers the password for the encryption certificate. None of these are exotic failures. They are gaps in routine.

This checklist covers the SQL Server backup best practices that matter in 2026: choosing the right recovery model, combining full, differential and transaction log backups to meet your recovery targets, backing up to Azure Blob Storage, using compression, encryption and checksums, testing restores, keeping immutable offsite copies, and monitoring the whole process so failures surface quickly.

The guidance applies to SQL Server 2019, 2022 and 2025. Azure SQL Database and Azure SQL Managed Instance run automated backups for you, so this article focuses on SQL Server you manage yourself, on premises or in virtual machines.

Start With RPO And RTO

Every backup decision follows from two numbers. The recovery point objective (RPO) is how much data the business can afford to lose, measured in time. The recovery time objective (RTO) is how long the business can wait for the database to be back online. A 15-minute RPO means you need a log backup at least every 15 minutes. A one-hour RTO means your restore sequence, including copying files from wherever they live, must finish inside an hour.

Get these numbers from the business owners of each system, not from the DBA team’s assumptions, and write them down. Different databases will have different targets. A customer orders database may need a five-minute RPO, while a reporting copy that can be rebuilt from source may tolerate a full day. Backups alone also have limits: if your RTO is measured in seconds, you need high availability features such as Always On availability groups on top of backups, not instead of them. For the bigger picture, our guide to disaster recovery in the database world covers how backups fit alongside replication and failover.

Choose The Right Recovery Model

The SQL Server recovery model controls how the engine manages the transaction log and which backup and restore options are available.

Full Recovery Model

Every transaction is fully logged, and the log is kept until you back it up. This is the only model that supports point-in-time restore and is required for availability groups and database mirroring. Use it for any database where losing more than the time since the last full or differential backup is unacceptable. The catch: if you use full recovery and never take log backups, the log file grows until it fills the disk.

Simple Recovery Model

SQL Server reclaims log space automatically at checkpoints, and you cannot take log backups. You can only restore to the point of a full or differential backup. This is a reasonable choice for development databases, staging areas and read-mostly data you can reload from elsewhere.

Bulk-Logged Recovery Model

Similar to full recovery, but certain bulk operations (such as index rebuilds and bulk imports) are minimally logged. It is meant to be switched on temporarily during large bulk operations. If a log backup contains minimally logged operations, you cannot restore to a point in time within that backup, so most teams use it sparingly or not at all.

Combine Full, Differential And Log Backups

Most production databases use all three backup types together.

• Full backups copy the entire database and enough of the log to make it consistent. They are the base of every restore sequence. Weekly or nightly is common, depending on size.
• Differential backups copy only the extents that changed since the last full backup. They shorten restores because you apply one differential instead of every log backup since the full. Differentials grow over time until the next full backup resets the base.
• Transaction log backups capture all log records since the previous log backup. They set your RPO and keep the log file from growing. Every 5 to 15 minutes is typical for important databases.

A typical schedule is a weekly full backup, a daily differential, and log backups every 15 minutes. A restore then needs the last full, the latest differential, and every log backup after that differential, applied in order. A break in the log chain (a missing or corrupt log backup) means you cannot restore past that point, which is why log backup monitoring matters so much.

Use COPY_ONLY for ad hoc full backups, such as a copy for a developer. A regular full backup taken outside your schedule becomes the new base for differentials, and if that file is later deleted or moved, your differentials become unusable.

Backup type	What it captures	Typical frequency	Needs full recovery model?
Full	Entire database	Weekly or nightly	No
Differential	Extents changed since last full	Daily or several times a day	No
Transaction log	Log records since last log backup	Every 5 to 15 minutes	Yes (or bulk-logged)
Copy-only full	Entire database, without resetting the differential base	Ad hoc	No

Back Up To URL And Azure Storage

SQL Server can write backups directly to Azure Blob Storage with BACKUP DATABASE … TO URL. This puts a copy offsite without a separate file transfer step. Microsoft recommends block blobs authenticated with a shared access signature (SAS) token stored in a SQL Server credential, and it supports striping a backup across multiple URLs to work around the per-blob size limit and speed up large backups. SQL Server 2022 updates also added Microsoft Entra managed identity authentication for backup to URL, for instances running on Azure VMs or enabled by Azure Arc.

SQL Server 2022 added backup and restore to S3-compatible object storage using the REST API, so the same approach works with on-premises object stores and other providers that implement the S3 API. This gives you an offsite or separate-system target without being tied to one cloud.

If you write backups to local disk first, copy them off the server quickly. A backup that lives only on the same host or storage array as the database protects against logical errors but not against losing that host.

Use Compression And Encryption

Backup Compression

Backup compression reduces backup size and usually shortens backup time, since less data is written. It costs extra CPU during the backup. Enable it at the server level with the backup compression default option, or per backup with WITH COMPRESSION. SQL Server 2022 added Intel QuickAssist Technology (QAT) as an alternative compression algorithm that can offload the work to hardware. Backups of databases protected by Transparent Data Encryption (TDE) can also be compressed, and current SQL Server 2019 updates and later versions no longer require you to set MAXTRANSFERSIZE to make that work.

Backup Encryption

Backup files contain all of your data, so treat them as sensitive. Native backup encryption (WITH ENCRYPTION) supports AES 128, AES 192, AES 256 and Triple DES, protected by a server certificate or asymmetric key. AES 256 is the sensible default. TDE also encrypts backups, since the data pages are already encrypted on disk.

The most important step is the one teams skip: back up the certificate and its private key and store them somewhere separate from the backups. Without the certificate, an encrypted backup cannot be restored on any other server, including your disaster recovery server. Test that restore path specifically.

Verify Backups With CHECKSUM And RESTORE VERIFYONLY

Add WITH CHECKSUM to every backup. SQL Server then verifies the page checksums on each page it reads (where page verification is set to CHECKSUM, the default for new databases) and calculates a checksum for the whole backup stream. If it finds a damaged page, the backup fails by default, which tells you about corruption early. You can enable it server-wide with the backup checksum default option so no job is missed.

RESTORE VERIFYONLY checks that a backup set is complete and readable, and if the backup was taken with CHECKSUM, it rechecks those checksums. It is a quick sanity check after each backup or after copying files to another location.

Neither of these proves the database will restore cleanly or that its contents are free of logical corruption. VERIFYONLY does not check the structure of the data inside the backup. That is what test restores and DBCC CHECKDB are for.

Test Restores On A Schedule

A backup you have never restored is an untested hypothesis. Schedule regular restores of production backups to a separate server, then run DBCC CHECKDB against the restored copy. This confirms that the files are usable, the restore sequence works, encryption keys are available, and the database is free of corruption, all without loading the production server.

Time each restore and compare it with your RTO. If restoring the full, differential and logs for your largest database takes three hours and the RTO is one hour, you have found a gap while it is still cheap to fix. Options include more frequent differentials, faster storage, striped backups, or adding a high availability layer.

At least once or twice a year, run a full recovery drill that includes people and runbooks, not just scripts. Restore to a point in time, restore a single database to a different instance, and practice the “tail-log” backup you would take from a damaged database to capture the last transactions before restoring.

Keep Offsite And Immutable Copies

Ransomware groups often target backups first, and an attacker with domain admin rights can delete backups stored on any share the server can write to. The long-standing 3-2-1 rule (three copies, on two different types of media, with one offsite) is still a good baseline, and most security teams now add a requirement that one copy be immutable or offline.

Azure Blob Storage supports immutability policies (time-based retention and legal holds) at the container or version level, and many S3-compatible stores offer object lock. Combined with backup to URL, this gives you a copy that cannot be modified or deleted during its retention period, even by an administrator. Make sure the account SQL Server uses to write backups cannot also change retention policies, and keep at least one copy in a separate account or subscription with separate credentials.

Set retention to match your recovery needs and any compliance requirements. Keep enough history to recover from corruption or a bad change that went unnoticed for weeks, not just from last night’s failure.

Monitor Backup Jobs

Backups fail quietly. A job that errors at 2 a.m. does nothing visible until the day you need it. Monitor several things:

• Job failures: configure SQL Server Agent to notify an operator on failure for every backup job.
• Backup age: query msdb.dbo.backupset for the most recent full, differential and log backup per database, and alert when any is older than its expected interval. This catches databases that were never added to a job, which failure alerts miss.
• Log reuse waits: check log_reuse_wait_desc in sys.databases. A value of LOG_BACKUP on a database means the log is waiting for a log backup, which often indicates a broken log backup job.
• Backup duration and size: sudden changes can signal storage problems, data growth or a compression setting that changed.
• Offsite copy status: confirm that copies actually reached the offsite and immutable targets.

Many teams use open-source maintenance scripts or a commercial backup product to handle scheduling, retention and reporting across many instances. If you are evaluating options, our list of the top SQL Server backup and recovery software compares the leading tools, and the top SQL Server monitoring tools roundup covers platforms that alert on missed backups alongside performance.

The 2026 Checklist

1. Document RPO and RTO for every production database with the business owner.
2. Set the recovery model per database to match its RPO.
3. Schedule full, differential and log backups that meet the RPO, and use COPY_ONLY for ad hoc backups.
4. Enable backup compression and backup checksum defaults at the server level.
5. Encrypt backups with AES 256 or use TDE, and back up certificates and keys separately.
6. Write a copy offsite with backup to URL or S3-compatible storage.
7. Keep at least one immutable or offline copy under separate credentials.
8. Run RESTORE VERIFYONLY after backups and after copies.
9. Restore to a test server on a schedule, run DBCC CHECKDB, and time it against the RTO.
10. Alert on job failures, stale backups and LOG_BACKUP reuse waits.
11. Run a full recovery drill at least once a year and update the runbook.

Frequently Asked Questions

How often should I back up the transaction log?

Match it to your RPO. For important databases, every 5 to 15 minutes is common. The more often you take log backups, the less data you can lose and the smaller each backup is.

Does RESTORE VERIFYONLY guarantee a backup is good?

No. It confirms the backup set is complete and readable and rechecks checksums if they were written. Only an actual restore followed by DBCC CHECKDB proves the database is recoverable and consistent.

Should I use the simple or full recovery model?

Use full recovery for any database where you need point-in-time restore or are using availability groups. Simple recovery suits development, staging and data you can reload from another source.

Can SQL Server back up directly to the cloud?

Yes. Backup to URL writes directly to Azure Blob Storage, and SQL Server 2022 and later can also back up to S3-compatible object storage.

Are SQL Server backups encrypted by default?

No. You need to specify WITH ENCRYPTION and a certificate or asymmetric key, or use TDE on the database. Either way, back up the certificate, or the backups cannot be restored elsewhere.

Conclusion

Good SQL Server backup best practices come down to a few habits done consistently. Set RPO and RTO first, choose recovery models and schedules that meet them, and use CHECKSUM, compression and encryption on every backup. Get copies offsite and make at least one of them immutable. Then prove it all works by restoring on a schedule and alerting the moment a backup goes missing. A backup plan that has been tested this way is one you can rely on during an outage, which is the only time it really matters.