Your Database Backups Are Probably Lying to You
Every business that has lost data had backups. A job that reports success only tells you a file was written. Six failures we find constantly, and the quarterly test that settles the question for good.
A Backup Is Not a Restore
Every business that has lost data had backups. That is not a paradox. A backup job that reports success is telling you a file was written. It is not telling you that the file can be read, that it contains what you think, or that anyone knows how to bring it back.
The only proof is a restore. If you have not done one, you do not have a backup. You have a hope with a file extension.
Here are the failures we find most often, roughly in the order we find them.
1. The Log Was Never Backed Up
The database is in FULL recovery model, someone set up a nightly full backup, and nobody set up log backups. Two things follow.
The transaction log grows until it fills the drive, because nothing is truncating it. And your recovery point is midnight, not five minutes ago, which is the entire reason for FULL recovery in the first place.
Check for it in one query:
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databasesA recovery model of FULL together with a log_reuse_wait_desc of LOG_BACKUP means exactly this problem. Either start backing the log up, or switch to SIMPLE and accept that you lose everything since the last full backup.
2. The Backups Live on the Same Machine
Local disk is not a backup destination. It is a staging area. A failed array, a ransomware event, or a flooded server room takes the database and the backups together.
The rule that has not changed: three copies, two kinds of media, one of them off-site and offline. Offline is the part that people skip and ransomware cares about most.
3. Nobody Verified the File Is Readable
Backup jobs have written corrupt files for years without complaint. Two settings turn this from luck into policy:
BACKUP DATABASE MyDb TO DISK = 'D:\Backups\MyDb.bak'
WITH CHECKSUM, INIT;
RESTORE VERIFYONLY FROM DISK = 'D:\Backups\MyDb.bak' WITH CHECKSUM;CHECKSUM makes SQL Server validate pages as it writes. RESTORE VERIFYONLY confirms the file is structurally readable afterward. Neither is a full restore test, but both are cheap and catch real failures.
4. The Corruption Was Backed Up Faithfully
If the database has been quietly corrupt for six weeks and you keep two weeks of backups, every file you have contains the corruption. Backups do not detect it. DBCC CHECKDB does, and it needs to run on a schedule and be read by a person.
Set it weekly at minimum. Alert on failure. A consistency error found on Tuesday is a support case. The same error found during a restore is a very different day.
5. Nobody Knows How Long a Restore Takes
Ask what the recovery time objective is and you usually get a number picked from the air. Ask how long the last restore took and you usually get silence.
A 400 GB database does not come back in ten minutes. Copying the file from off-site storage may take longer than the restore itself. Neither number is knowable without doing it once.
6. The Restore Depends on One Person
The person who knows the encryption certificate, the storage credentials, and the order the databases come back in is on a plane. Write it down. Where the files are, who has access, which system comes back first, and how the application is pointed at the restored instance.
A runbook that a competent stranger can follow at 2 a.m. is the deliverable. Anything less is a single point of failure with a pulse.
The Test That Settles It
Once a quarter, on a non-production server:
- Restore the most recent full backup, plus differentials and logs, to a point in time
- Time every step and write the numbers down
- Run
DBCC CHECKDBagainst the restored copy - Run a business query against it and have someone who knows the data confirm the results look right
- Update the runbook with what surprised you
Step 4 is the one that catches the ugliest problem of all: a backup that restores perfectly and is missing the database that the application actually needs.
We Will Run the Test for You
If it has been more than a year since anyone restored, that is worth fixing before you need it. We review backup strategy, prove the restores, and leave you with a runbook your team can execute. Get in touch.
