The backup job completed successfully. That means SQL Server wrote a file. It does not mean the file is intact, that the backup chain is complete, that anyone knows where the file is at three in the morning, or that a restore fits within the downtime the business can tolerate. The only way to find out is to restore on purpose, before you have to.

First, agree on the targets: RPO and RTO

Two concepts drive any backup strategy, and they should be set with the business, not by IT alone.

  • RPO (Recovery Point Objective): how much data the company can afford to lose, measured in time. If the answer is “15 minutes at most,” you need log backups at least that often, with the database in the FULL recovery model.
  • RTO (Recovery Time Objective): how long the system can be down before it must be working again. This covers finding the files, copying them, restoring, fixing access and handing the system back to users.

RPO is designed through backup frequency. RTO can only be found by measuring it. This is where restore testing stops being paperwork and becomes information for decisions.

What tends to go wrong when it matters

Problems a test exposes and a green job hides:

  • A broken log chain. The database was switched to SIMPLE at some point, or another tool takes log backups and stores them somewhere else. The sequence you have does not line up.
  • A changed differential base. Someone took an ad hoc full backup without COPY_ONLY. Every differential after it depends on that file, which may have been deleted.
  • A missing certificate. Databases with TDE, or encrypted backups, can only be restored on another server if the certificate and its private key were backed up too.
  • Retention that is too short. The cleanup routine deleted the full backup the log backups depended on.
  • Everything in one place. A backup on the same disk or server as the database disappears along with it.

The test routine, step by step

1. Pick the database and the target

Always restore to another server or, at the very least, under a different name and to different files. Never over the production database. A test server with enough disk space will do. Start with the most critical databases and rotate the rest over the following months.

2. Find the backup sequence in msdb

SELECT b.database_name,
       b.type,
       b.backup_start_date,
       b.backup_finish_date,
       b.first_lsn,
       b.last_lsn,
       b.is_copy_only,
       m.physical_device_name
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_id
WHERE b.database_name = N'Sales'
  AND b.backup_finish_date >= DATEADD(DAY, -7, SYSDATETIME())
ORDER BY b.backup_finish_date;

In the type column, D is full, I is differential and L is log. physical_device_name shows where each file was written. If the msdb history is not available, the test has to start by working out where the files are, and that alone is a finding.

3. Check the files before restoring

RESTORE HEADERONLY shows what a backup file contains, and RESTORE FILELISTONLY lists the data and log files the database will need. RESTORE VERIFYONLY checks that the backup set is complete and readable. If the backup was taken WITH CHECKSUM, it also validates page checksums. Even so, it does not replace a real restore: a file can pass VERIFYONLY while a piece of the sequence is still missing.

4. Restore the full sequence

RESTORE DATABASE Sales_Test
  FROM DISK = N'\\backup\sql\Sales_full.bak'
  WITH MOVE N'Sales'     TO N'D:\Test\Sales_Test.mdf',
       MOVE N'Sales_log' TO N'L:\Test\Sales_Test_log.ldf',
       NORECOVERY, STATS = 10;

RESTORE DATABASE Sales_Test
  FROM DISK = N'\\backup\sql\Sales_diff.bak'
  WITH NORECOVERY;

RESTORE LOG Sales_Test
  FROM DISK = N'\\backup\sql\Sales_log_1400.trn'
  WITH NORECOVERY;

RESTORE DATABASE Sales_Test WITH RECOVERY;

Every intermediate step uses NORECOVERY, and only the last one brings the database online. To simulate recovering to a specific moment, add STOPAT to the final RESTORE LOG. The logical file names used in MOVE come from FILELISTONLY.

5. Check the integrity of the restored database

DBCC CHECKDB (Sales_Test) WITH NO_INFOMSGS, ALL_ERRORMSGS;

No error messages means the database is consistent. Running CHECKDB on the restored copy has a side benefit: it moves part of the integrity-checking load off production, as long as the copy is recent and the test runs often.

6. Validate from the business side

A consistent database is not necessarily a usable one. Check:

  • the timestamp of the most recent row in the main tables, compared with the time of the last log backup restored;
  • row counts for a few tables, compared with production at the same point in time;
  • whether the application can connect with its own login and perform a simple operation.

That last check often uncovers orphaned users: the user exists in the restored database, but the matching login on the target server has a different SID or does not exist at all.

7. Time it

Time the whole thing from the restore request until the application works, not just the RESTORE command. Add the time spent finding the files and copying them from offsite storage. That figure is your real RTO. If it exceeds the target, a decision is needed: invest in infrastructure (faster storage, high availability, a replica) or revisit the target with the business.

What the database backup leaves out

Restoring the user database is not enough to bring the system back. Add to your checklist:

  • server logins, with the correct SIDs;
  • SQL Server Agent jobs, operators and alerts (they live in msdb);
  • linked servers and credentials;
  • certificates and keys used by TDE or backup encryption;
  • instance settings and application connection strings.

Regular backups of master and msdb, plus login and job creation scripts kept off the server, cover much of this.

Automate the repetitive parts

Once the manual runbook works, it can become a script. The community PowerShell module dbatools, for example, includes Test-DbaLastBackup, which restores the latest backup to another instance, runs CHECKDB and drops the copy. Automation does not remove the need for human review: someone still has to read the results and act when a test fails.

A reasonable cadence is to test critical databases monthly and, every quarter, run a full exercise that includes logins, jobs and the application.

Write it down for whoever is on call

Real restores happen under pressure, often handled by someone who did not build the routine. The runbook should answer these questions without relying on memory:

  • where the backups and offsite copies are, and how long they are kept;
  • the restore order and the commands, with real paths;
  • where certificates and credentials are stored (the vault location, not the password);
  • who decides to restore and who needs to be told;
  • the test history: date, database, measured time, issues found and what was fixed.

That history also helps in audits, because it shows that recovery has been tested rather than just planned.

Want to know whether your backups will come back?

If the last restore test was a long time ago, or never happened, an assessment is a good first step. See how Trustwise handles backup and continuity in SQL Server database management and data infrastructure, or request an assessment.