SQL Server Backup Will Not Restore: What to Try

When a SQL Server restore fails, the temptation is to try again, then try another tool, then start deleting things. It is usually better to read the error, because restore failures tend to fall into a small number of categories, and each one has a different fix. The one thing to avoid throughout is altering or overwriting the only copy of the backup file.

Make a copy of the backup file, or of every file in the backup set, before experimenting. A backup can be striped across several files, and if any of them is missing the restore cannot complete. Note the file size and the date, and compare them with what you expect, because a transfer that was interrupted often leaves a file that looks fine but is truncated.

RESTORE HEADERONLY shows what the backup contains, including the database, the SQL Server version that made it, and the backup type. RESTORE FILELISTONLY lists the files it would restore. RESTORE VERIFYONLY checks that the backup set is complete and readable, although it does not fully validate the data inside, so a backup that passes it can still fail to restore. If the backup was taken with the CHECKSUM option, a restore can also validate page checksums as it runs.

A backup can be restored to the same or a newer version of SQL Server, but not to an older one. If the backup used encryption, or the database used Transparent Data Encryption, the certificate and its private key are needed on the target server, and without them the restore fails no matter how healthy the file is. Differential and log backups must also match the right full backup and follow one another in sequence, so a restore that fails partway through is often a broken chain rather than a damaged file.

For a backup that is genuinely damaged, RESTORE with CONTINUE_AFTER_ERROR will restore what it can read and carry on past errors. The result can be an inconsistent database, so treat it as a way of extracting data: run it on a scratch server, then check the outcome with DBCC CHECKDB, and never on the production instance.

If the file is truncated, partly unreadable or missing pieces, the data inside can sometimes still be read directly from the backup file, table by table, even when a normal restore refuses. How much is recoverable depends on how much of the file survives, and we tell you exactly what is recoverable after a free assessment. Keep the original file untouched and work from a copy.

Restore a backup to a test server on a regular schedule, take backups with CHECKSUM, keep a copy of any certificates used for encryption somewhere separate, and store a second copy of important backups off the main system. The moment a backup is really tested is much better before it is needed.