How to Recover SQL Server Database from a Corrupt Backup File?
Summary: This blog is intended for Microsoft SQL server users looking to extract data from a corrupt backup (.bak) file for restoring SQL database. It outlines the reasons behind corruption, and describes occurrences of a damaged .bak file reported by users. It further discusses the process on ‘How to Recover SQL Server Database from a Corrupt Backup file?’ by using Stellar Toolkit for MS SQL.
When it comes to extracting data from an SQL database, users rely on the .bak file to restore the data. Unfortunately, the file can become corrupt and makes it difficult for users to recover the database.
Let’s look at a few user instances reporting about damaged backup file:
User Instance 1: “Recently I encountered a system crash. All my data including the main backup file was damaged. I had created a backup copy in my external hard drive. So, I formatted my machine and tried to restore the data from the backup present in the external hard drive. But when trying to open the drive, I receive an error message saying, ‘the backup file contains unrecognized data and cannot be used’. I don’t understand this error, please help! I cannot afford to lose my data saved inside the backup file.”
User Instance 2: “Restoring database from backup using SQL Server Management Studio (SSMS) is showing that some information is lost. Is the backup file corrupt? If so, how can I recover it?”
What Causes Corruption in a .BAK File?
The file can turn corrupt because of the following reasons:
- Abnormal system termination or crash, when the database is open.
- Device used for storing the backup file is virus infected.
- Bugs in SQL server can also cause corruption in the .bak file.
Irrespective of the reason behind corruption, inability to restore database from the backup increases risk of losing mission-critical data.
How to Recover a Database From a Corrupt .bak file?
There is no manual method that supports recovery of a damaged SQL database backup file. But, you can easily and quickly recover it by using Stellar Toolkit for MS SQL software. Essentially, the software provides a backup recovery tool to help users extract data from different types of SQL server backups – be it Full, Differential, or Transactional.
NOTE: Make sure you meet the following prerequisites before running Stellar Toolkit for MS SQL software:
- You must have SQL Server installed and running on your PC.
- SQL Server services must be running.
Steps to Recover Corrupt Backup File
Step 1: Download, install and launch Stellar Toolkit for MS SQL software.
Step 2: From the software’s user interface, click the Extract from MS SQL Backup box.
Step 3: The Stellar Backup Extractor for MS SQL window is displayed. Click Select File to choose the backup file you want to repair.
NOTE: Click ‘Search in Folder’ if you are not aware of the file location.
Step 4: Once you have selected the .bak file, click Scan to initiate the scanning process.
Step 5: Select BackupSet dialog box appears with details of all the backups taken in different backup modes.
Step 6: Select the .bak file you need to recover from the Backup Type list, and then click Next to begin the scanning process.
NOTE: In case the selected Backup is located in multiple files, a window showing the same is displayed. Click ‘Add’ to select multiple file locations.
Step 7: After completion of the database scan, a window appears displaying the number of total records available in the backup file as Logs.
NOTE: The scanning process can take time. To stop the process, click ‘Skip this step’.
Step 8: The software displays a preview of the number of database records.
Step 9: To save the recovered file, click Save on File menu.
Step 10: In the window that appears, select MSSQL under Save As section, and then choose Browse to select the destination to save the recovered file. Click OK.
Step 11: Select New Database or Live Database under Saving Option. Next, fill in the required details under Connect to Server section, and then click the Connect button.
Step 12: Once ‘Recovery process successfully completed’ message appears, click OK.
The recovered file will be saved in the selected location.
SQL Server database backup can become corrupt due to reasons like abrupt system shutdown, virus attack, software bugs, etc. The post explained the step-by-step procedure on ‘How to Recover SQL Server Database from a Corrupt Backup File?’ with Stellar Toolkit for MS SQL software.
The software is an all-in-one toolkit for SQL users that not only help extract data from damaged .bak files, but also provides utilities to repair corrupt SQL databases and reset lost passwords of administrators and users.