How often should SQL Server backups be tested?

Most organisations running SQL Server can show that their backups run. Far fewer can show they can restore them on another server, within the time the business expects, with the application working. Only the second matters on the day something goes wrong. This article covers what a backup job proves, what a real restore test involves, and how often to run one.

A successful backup job is not proof of recovery

A green tick in SQL Server Agent or a backup product means a backup command completed and wrote a file. It does not mean that:

• the file still exists, has been copied off the server and has not been removed by retention;
• every log backup needed to reach your recovery point exists;
• the data in the backup is free of corruption;
• the certificates needed for an encrypted backup are held somewhere other than the server you have lost;
• the restore fits within the time the business can tolerate;
• the logins, jobs and linked servers the application relies on can be recreated;
• the job covers every database, including recently added ones.

The only way to answer those questions is to restore the backups and use the result.

RPO, RTO and the backup chain

• Recovery point objective (RPO) is how much recent work the business can afford to lose. If fifteen minutes of orders is survivable but an hour is not, log backups need to run at least every fifteen minutes.
• Recovery time objective (RTO) is how long the system can be unavailable, from the decision to restore until people are working again, not just the RESTORE command.

Agree both with the people who own the application rather than letting IT assume them. A restore test is measured against them: did it reach the expected point in time, within the RTO?

SQL Server has three main backup types:

• Full: the whole database, plus enough log to make it consistent.
• Differential: changes since the last full backup not taken as copy-only. An ad hoc full backup without COPY_ONLY becomes the new differential base, which matters if that file is later deleted.
• Log: log records since the previous log backup, in the FULL and BULK_LOGGED recovery models only.

A typical restore uses the latest full backup, the latest differential based on it, then every later log backup in order. That unbroken sequence is the log chain, and you cannot restore past a missing log backup. Switching to SIMPLE breaks the chain, and a log backup taken by another tool leaves a file your restore process may not know exists.

What msdb, VERIFYONLY and CHECKSUM can tell you

Backup history in msdb

SQL Server records backups in msdb: backupset has a row per backup and backupmediafamily records where each was written. This shows the latest full, differential and log backup per database, ignoring copy-only backups:

SELECT d.name AS database_name,
       d.recovery_model_desc,
       MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full,
       MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_differential,
       MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.database_name = d.name
   AND bs.is_copy_only = 0
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

A NULL in last_full, or a FULL database with no recent last_log, needs attention now. The next query shows where recent backups went, how long they took, and whether they used checksums or encryption. Striped backups return a row per file.

SELECT bs.database_name,
       bs.type,
       bs.backup_start_date,
       DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds,
       bs.compressed_backup_size / 1048576.0 AS compressed_size_mb,
       bs.has_backup_checksums,
       bs.is_damaged,
       bs.is_copy_only,
       bs.key_algorithm,
       bs.encryptor_type,
       bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.backup_start_date >= DATEADD(DAY, -7, SYSDATETIME())
ORDER BY bs.database_name, bs.backup_start_date DESC;

To look for gaps in the log chain, compare each log backup’s first_lsn with the previous last_lsn; in an unbroken chain they match.

SELECT database_name,
       backup_start_date,
       first_lsn,
       last_lsn,
       LAG(last_lsn) OVER (PARTITION BY database_name
                           ORDER BY backup_start_date) AS previous_last_lsn
FROM msdb.dbo.backupset
WHERE type = 'L'
  AND is_copy_only = 0
  AND backup_start_date >= DATEADD(DAY, -14, SYSDATETIME())
ORDER BY database_name, backup_start_date;

Treat msdb as a record of what SQL Server did, not of what you can restore. It lives on the server you are protecting, can be purged with sp_delete_backuphistory, cannot tell whether files still exist, and in an availability group each replica records only its own backups.

RESTORE VERIFYONLY and CHECKSUM

RESTORE VERIFYONLY reads a backup without restoring it. It checks the backup set is complete and readable, checks some page header fields, validates checksums if present, and checks destination space. Microsoft is explicit that it does not verify the structure of the data.

Backing up WITH CHECKSUM is still worthwhile: SQL Server verifies page checksums as it reads, stops if one fails, and adds a checksum for the backup itself. It is off by default except for compressed backups, and the backup checksum default option enables it for the whole instance. Page checksums only cover pages that have them, which depends on the PAGE_VERIFY setting.

Both give early warning. Neither is a restore test: they cannot show logical consistency, a complete log chain, a suitable target server or how long recovery takes.

What a real restore test involves

1. Restore to a separate server. Testing on the production instance risks overwriting the live database, and a separate server proves nothing depends on the original host.
2. Restore from the copy you would really use, usually the off-site or secondary copy, since the local backup folder may be lost with the server.
3. Restore the whole sequence of full, differential and log backups to a chosen point in time, using the CHECKSUM option if the backups have checksums.
4. Run DBCC CHECKDB on the restored copy with NO_INFOMSGS, and treat any error as a failure. A clean result shows the backup’s data was consistent. It says nothing about pages that go bad on production storage later, so it does not replace production integrity checks.
5. Check the data and the application. Confirm the latest transactions match the expected recovery point, compare row counts on key tables and, where possible, point a test copy of the application at the restored database. Check that logins map to users and that required jobs, linked servers and credentials can be recreated.
6. Time it against the RTO, stage by stage: retrieving files, restoring, recovery, checks and reconnecting the application. Large log files, high VLF counts and slow links to backup storage all add time.
7. Record the result: date, backups used, recovery point, duration and problems. On the test server, msdb.dbo.restorehistory shows which restores ran and when.

Off-site and immutable copies

Backups on the same server, storage or domain as production can be lost with it, through hardware failure, mistaken deletion or ransomware that encrypts every share it reaches. Keep at least one copy off-site and one that cannot be altered or deleted during its retention period, such as immutable object storage or offline media. Document how to retrieve files from them somewhere other than the systems being protected, and run some test restores from those copies.

Encryption keys and certificates

Backups of a database using transparent data encryption (TDE) are encrypted too. Restoring them elsewhere needs the certificate protecting the database encryption key, with its private key; without them, the backup cannot be restored. Microsoft advises backing these up as soon as TDE is enabled, and keeping the certificate after TDE is turned off, because older backups still need it. This query shows when each TDE certificate’s private key was last backed up; NULL means never.

SELECT DB_NAME(dek.database_id) AS encrypted_database,
       c.name AS certificate_name,
       c.pvt_key_last_backup_date
FROM master.sys.certificates AS c
JOIN sys.dm_database_encryption_keys AS dek
    ON c.thumbprint = dek.encryptor_thumbprint;

Backup encryption, available from SQL Server 2014, has the same dependency on its certificate or asymmetric key. Renewing a certificate can change its thumbprint, so keep the original while backups encrypted with it are retained; encryptor_thumbprint in backupset shows which one each backup needs. Store certificate backups and their private key passwords separately from the database backups.

How often should backups be tested?

There is no single correct interval. It depends on how critical the database is, how often it and its surroundings change, and how much certainty the business needs. As a starting point, we suggest three layers, adjusted to your own risk:

Test	What it proves	Suggested rhythm
Automated restore test	The latest backups restore and pass DBCC CHECKDB	Weekly for business-critical databases, more often if tooling makes it cheap; monthly for less critical systems
Full recovery exercise	A point-in-time recovery from off-site copies, with application checks and timings, meets the RPO and RTO	Quarterly or twice a year for critical systems; at least once a year for others
Change-driven test	A specific change has not broken recovery	After any significant change

Automated tests catch common failures cheaply. The fuller exercise catches what automation misses: server-level objects, undocumented steps, access to off-site storage, and whether people know what to do. The more a failed recovery would cost, the more often it is worth testing.

Significant changes worth a test include:

• SQL Server upgrades, or migration to new servers, storage or a cloud platform;
• new backup software, destinations, retention or schedules;
• enabling TDE or backup encryption, or replacing their certificates;
• recovery model changes, or new databases or availability groups;
• changes to service accounts, domain membership or share permissions.

What testing tends to uncover

• Databases missing from the backup job, often ones created after it was set up.
• Log chain gaps from a recovery model change or a second tool taking log backups.
• Unusable differentials, because an ad hoc full backup reset the base and its file was deleted.
• Files removed by retention sooner than expected, or off-site copies that quietly stopped updating.
• Missing TDE or backup encryption certificates, or private key passwords nobody can find.
• Restores far slower than the RTO because of copy times, very large log files or high VLF counts.
• Target servers without enough disk space, or on an older SQL Server version, which cannot restore backups from a newer one.
• Orphaned users and missing logins or Agent jobs, so the database restores but the application cannot use it.
• Corruption found by DBCC CHECKDB that was already present when the backup was taken.

If you cannot say when your backups were last restored, how long it took, or where the encryption certificates are kept, a backup and recovery review is a sensible next step. It compares your RPO and RTO with what your backups can actually deliver, and leaves you with a testing routine you can keep up.