Can you restore your SQL Server databases? How to prove it

A backup job that finishes green proves one thing: a file was written. It does not prove that the database inside that file will come back, that it will come back to the point in time the business expects, or that it will come back fast enough to matter. Databases fail to restore in ways that ordinary file restores do not, so they deserve their own test.

Restore testing is the first item in our database health check, and it builds on the wider advice in backups that survive ransomware.

What a successful backup job does not tell you

Corruption gets backed up too. If a data page is damaged, the backup faithfully copies the damage. Unless something checks the database itself, nobody finds out until the restore.
The log chain may be broken. Point-in-time recovery needs an unbroken sequence of transaction log backups. A gap, a manual backup taken to the wrong place or a change of recovery model can quietly break it.
The keys may be missing. If a database uses Transparent Data Encryption or backup encryption, the certificate and its private key must be backed up and stored separately. Without them, the backup cannot be restored on another server.
The backup may share the database’s fate. Backups on the same server, the same disk or behind the same administrator account can be lost in the same incident.
Retention may be shorter than detection. If corruption or tampering goes unnoticed for longer than you keep backups, every copy you hold may already be affected.

Check the recovery model matches the promise

Before testing anything, compare each database’s recovery model with what the business believes it can recover to.

Simple recovery restores to the last full or differential backup. If those run nightly, a failure late in the day can cost a full day of work.
Full recovery with regular log backups allows a restore to a point in time, limited by how often the log is backed up.

If the business expects to lose no more than 15 minutes of data and the database is in simple recovery, that gap needs fixing before any test is meaningful.

Restore it, do not just verify it

RESTORE VERIFYONLY checks that a backup set is complete and readable, and it validates page checksums if the backup was taken WITH CHECKSUM. That is a useful quick check, but it does not prove you have a working database.

A real test looks like this:

Restore the latest full backup, plus any differential and log backups, to a separate server under a different database name.
Run DBCC CHECKDB on the restored copy to confirm it is structurally sound.
Run a few queries the application depends on, and ideally point a test copy of the application at it.
Record how long each step took, from finding the backup files to the application working again.

Running DBCC CHECKDB on the restored copy also moves the heavy consistency check off production.

The same principle applies beyond SQL Server. Azure SQL takes automated backups and supports point-in-time restore within the retention period you configure, but a periodic restore to a new database is still the only way to confirm the result. Oracle’s RMAN can validate backups without restoring them, which is useful between full tests rather than instead of them. For MySQL, restore a dump or physical backup to a test instance and check it starts and answers queries.

Measure the two numbers that matter

Every restore test should produce two figures, compared against what the business has agreed:

Recovery point: how much data, measured in time, the restore lost.
Recovery time: how long it took to get back to a working state.

Large databases often take longer to restore than anyone expects, and replaying a day of log backups adds more. If the measured recovery time is longer than the business can be down, that is a design problem to solve now, not during an outage.

What to do this quarter

Confirm each database’s recovery model and log backup frequency against the recovery point the business expects.
Back up any encryption certificates and private keys to a separate, secured location, and test restoring them.
Run a full restore to a separate server, check it with DBCC CHECKDB, time it, and write down the result.

A restore you have actually performed is evidence; a backup report is not. If you would like this done for you, start with a database health check.