SQL Server Backup Restore Fails: Real Fixes That Work

1. File Path Mismatch — The One That Gets Everyone

You’re sitting there, you’ve got a good .bak file from last night, and the restore fails with something like System.Data.SqlClient.SqlError: The media family on device 'D:\Backups\CustomerDB.bak' is incorrectly formed. Or worse, it just says “Restore failed” with no real detail. Nine times out of ten, it’s a file path mismatch.

Here’s what happened. The backup was taken on Server A where the data files lived at E:\SQLData\. You’re now restoring on Server B where SQL Server’s default data directory is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\. SQL Server is trying to put the files in the exact same path they were on the original server, and that path doesn’t exist on the new machine. So it throws up its hands.

Had a client last month whose entire print queue died because of this — they restored a backup from an old server onto a new one, and the restore script didn’t include the WITH MOVE option. Took them two hours to figure out what I catch in 30 seconds. Don’t be that guy.

The Fix

First, figure out the logical file names inside the backup. Run this:

RESTORE FILELISTONLY FROM DISK = 'D:\Backups\YourDatabase.bak';

You’ll get a result set with columns LogicalName and PhysicalName. Copy those logical names. Then use them in your restore statement:

RESTORE DATABASE YourDatabase
FROM DISK = 'D:\Backups\YourDatabase.bak'
WITH MOVE 'YourDatabase_Data' TO 'C:\SQLData\YourDatabase.mdf',
MOVE 'YourDatabase_Log' TO 'C:\SQLLogs\YourDatabase_log.ldf',
REPLACE;

Yeah, the REPLACE option overwrites the existing database. Use it only when you’re sure you want to blow away the current copy. If you’re restoring over a database that’s still in use, back it up first. I don’t care if you think you don’t need to — do it.

If you’re doing this through SSMS, go to the Options page in the restore dialog and check “Overwrite the existing database” and set the file paths in the Files tab manually. Same thing, but point and click.

2. Corrupted Backup File — More Common Than You Think

Sometimes the backup file itself is busted. Maybe the drive where it was saved had a bad sector. Maybe the backup process was interrupted by a power flicker. Maybe the network transfer got a bit flipped. I’ve seen backups that looked fine in file size but were useless because the backup software didn’t do a checksum.

You’ll know it’s corruption because the restore error will often include Backup or restore operation terminating abnormally or a checksum mismatch message. If you didn’t run WITH CHECKSUM when you took the backup, you won’t even get a clear error — just a vague failure. That’s the worst kind.

The Fix

Before you restore, verify the backup file integrity. Run:

RESTORE VERIFYONLY FROM DISK = 'D:\Backups\YourDatabase.bak';

If it returns 1 (success), the backup is probably OK. If it throws an error, the file is toast. But here’s the dirty truth: VERIFYONLY doesn’t fully check every byte — it only validates the header and some page-level checksums. For a thorough check, restore to a test database first, like this:

RESTORE DATABASE TestRestore
FROM DISK = 'D:\Backups\YourDatabase.bak'
WITH MOVE 'YourDatabase_Data' TO 'C:\Temp\TestRestore.mdf',
MOVE 'YourDatabase_Log' TO 'C:\Temp\TestRestore_log.ldf';

-- If it succeeds, drop the test database
DROP DATABASE TestRestore;

If that fails, grab another copy of the backup. If you don’t have one, you’re in a bad spot. That’s why I always tell my clients: enable WITH CHECKSUM in their backup scripts and store backups in two separate locations. Had a lawyer’s office lose a week of billing data because they relied on a single backup that turned out corrupt. Not fun.

If you can’t get a clean backup, try restoring with CONTINUE_AFTER_ERROR:

RESTORE DATABASE YourDatabase
FROM DISK = 'D:\Backups\YourDatabase.bak'
WITH CONTINUE_AFTER_ERROR;

This gets you the database online, but it’ll be marked as suspect. You can then run DBCC CHECKDB to see how bad the damage is. Sometimes you can salvage most of the data. Sometimes you can’t. Better than nothing.

3. Permission Issues — The Silent Killers

This one gets overlooked because the error message is often cryptive. It might say something like Cannot open backup device 'D:\Backups\YourDatabase.bak'. Operating system error 5(Access is denied.) or just “Restore failed for server”. The SQL Server service account doesn’t have read/write access to the backup file or the destination folder.

I had a client who was trying to restore to a network share — the backup sat on \\NAS\Backups\. SQL Server was running as NT SERVICE\MSSQLSERVER, but the NAS only allowed domain admin accounts. Took them three days to figure out. I told them to copy the .bak file to a local temp folder. Worked instantly.

The Fix

Check the SQL Server service account. Open SQL Server Configuration Manager, click on SQL Server Services, look at the Log On As column for your instance. It’s usually NT SERVICE\MSSQLSERVER or NT AUTHORITY\NETWORK SERVICE.

Grant that account full control to the folder containing the backup file and the destination data folder. On the backup file itself, right-click, go to Properties > Security > Edit, add the service account, and give it Read permission. On the destination folder (e.g., C:\SQLData\), give it Modify permission.

If you’re using a network share, you need the computer account of the SQL Server machine to have share permissions. For a server named SQLSRV01, the share permission needs to go to SQLSRV01$ (the computer account). Or just copy the backup locally — it’s ten times faster and avoids years of network headaches.

After setting permissions, restart the SQL Server service. You don’t always have to, but I’ve seen cases where permissions don’t take until the service restarts. Better safe than sorry.

Quick-Reference Summary Table