SQL Interview: 101 Restoring successful backups

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

If you have successfully created a SQL Server database backup, what could cause you to not be able to restore it, on the same server or another server ?

Answer:

A successful SQL Server backup does not guarantee that it can later be restored successfully. A few important points explain why:

What a successful backup actually means

When SQL Server reports a backup as successful, it only confirms that:

• The backup completed without I/O or corruption errors during the backup process.
• The backup file was written successfully to the target location.
• SQL Server did not detect any problems reading the database pages it backed up.

However, this doesn’t prove the backup file itself is readable or complete under all conditions, or that it’s restorable in another environment.

Common reasons a backup might fail to restore

• File corruption after backup
• Disk, network, or antivirus interference can damage the .bak file after it’s written.
• Cloud sync tools (OneDrive, Dropbox) can sometimes partially sync large files.
• Incomplete or interrupted backup chain
• If you use differential or log backups, restoring requires all relevant backups in sequence. Missing or mismatched files can make the chain unusable.
• Version or edition mismatch
• You can’t restore a backup from a newer SQL Server version to an older one.
• Some features (e.g., TDE, compressed backups) may require matching editions or keys.
• Corruption not detected during backup. Unless you use WITH CHECKSUM, SQL Server may not detect all page corruptions while backing up.
• Storage-level or OS-level corruption
• Backup file stored on faulty media may later be read incorrectly.

2025-12-26