SQL BAK File Recovery: Getting Data Out of a Corrupted or Ransomware-Encrypted SQL Server Backup

When the database is gone and the backup will not restore, the .bak file is often the last copy of the data. RESTORE is strict: one damaged area, a missing header or a few encrypted megabytes and it stops with an error, even though most of the database is still in the file. This guide explains what a SQL Server backup file contains, what the restore errors mean, what to try first, and how SQL BAK file recovery works when RESTORE cannot finish.

A SQL Server full backup is written in Microsoft Tape Format: a set of headers that describe the media, the backup set and the database files, followed by the data itself, and at the end the part of the transaction log needed to make the restored database consistent. The data part is, very largely, a copy of the database's own 8 KB pages, each with the same header it has in the MDF: file and page number, object, page type and, on most databases, a checksum.

Compressed backups store the same pages in a series of independently compressed chunks, and backups made WITH ENCRYPTION, or of a database with Transparent Data Encryption, store them encrypted with a key held in a certificate on the server.

This is why a damaged backup is rarely an all-or-nothing loss. The headers are small and sit at the start of the file; the pages are everywhere else. A backup whose headers are destroyed cannot be restored by SQL Server, but its pages can still be found one by one, checked against their own header and checksum, and assembled into tables.

Error 3241, 'the media family is incorrectly formed', means SQL Server cannot read the backup's headers: the start of the file is damaged, overwritten, encrypted by ransomware, or the file is incomplete or not a SQL Server backup at all. Error 3183, 'RESTORE detected an error on page', means the headers were fine but one or more pages inside failed their check. Error 3203 means Windows reported a read failure on the backup file or device, which points to the storage underneath. Error 3169 is different: the backup came from a newer version of SQL Server than the one restoring it, and the backup itself may be perfectly healthy.

Error 3013, 'RESTORE DATABASE is terminating abnormally', only says that the restore stopped; the useful message is the one just before it.

Copy the .bak file to a separate disk and work on the copy. Run RESTORE HEADERONLY and RESTORE FILELISTONLY: if they answer, the headers are readable and the problem is inside the data. RESTORE VERIFYONLY checks that the whole backup can be read, and if the backup was taken WITH CHECKSUM it checks every page checksum too.

If only some pages are damaged, RESTORE ... WITH CONTINUE_AFTER_ERROR restores what it can instead of stopping at the first bad page. Restore it under a new database name, never over a database you still need, and run DBCC CHECKDB on the result before trusting it: the damaged pages are still damaged, and the tables that used them may be incomplete. If there are several backups, try the older full backup too, together with its differential and log backups.

When the headers are gone or too much is damaged for RESTORE, the backup can be searched for page images directly. Each candidate page is checked against its own header and checksum, the newest valid copy of every page is kept, the system catalog pages are decoded to rebuild the list of tables and columns, and the rows of every table are read from its data pages, ready to be written into a new database or exported to files. Compressed backups are decompressed chunk by chunk, so damage to one chunk does not stop the chunks after it.

The same approach can also turn a damaged backup back into an ordinary MDF file, page by page, so that a normal attach or a recovery tool can work on it.

Backup files are a favourite target, and because they are large, many ransomware families encrypt only parts of them to finish quickly. We have measured, for example, a 171 GB backup in which about 9% of the file was encrypted in three stretches, and an accounting backup encrypted only in its first and last 2 MB; from the second, all 226 tables and 1.34 million rows were read from the remaining pages. In a LockBit case, ERP backups were recovered to 354 tables and 98.8% of the rows, checked against a healthy copy.

Two lessons from those cases. A later backup of the same database does not always help, because the ransomware tends to encrypt the same positions in every file. A healthy database with the same structure, such as a sister company on the same application, often helps more: it supplies table names and structure where the encrypted catalog pages left gaps. And backups made WITH ENCRYPTION or from a TDE database need the original certificate; without it their pages cannot be read, ransomware or not.

AS SQL Recovery & Repair Studio opens .bak files directly, including compressed and partly encrypted ones, shows every table and row it can recover, and can convert a backup into an MDF file. The trial analyses the backup and exports the first rows of each table, so you can see the result before buying. If the backup files were deleted or sit on an encrypted or formatted disk, the free AS SQL Deleted Database Recovery tool finds them and packs them into one encrypted image for our laboratory.

For business-critical systems, our SQL Server database recovery service works remotely or in our laboratory, measures exactly how much of the backup is intact, and gives you a free assessment of what can be recovered before any work is paid for. Afterwards, the best protection is simple: take backups WITH CHECKSUM, keep a copy off the main server, and restore one to a test server on a schedule.