Several reasons can cause corruption in the SQL database files (MDF/NDF). It can happen because of integrity issues, power outages, abrupt or improper system shutdowns, malware attacks, network failures, etc. The simplest method to recover data from any corrupted database is to restore it from a backup (.bak) file.
At times, even backups can become corrupted, which prompts you to take further action, such as using a third-party tool. Here is detailed information on this topic. Let’s start by discussing the importance of backup.
Restore data from a corrupt SQL Server Database from the BAK File
The methods discussed here can help to restore data from a corrupted database with the help of backup files.
1. Using SQL Server Management Studio (SSMS)
Microsoft SSMS provides a user-friendly GUI that allows for several SQL functions. It also lets you restore the SQL database from the backup file. Here are the steps for the same:
- Launch SSMS.
- Connect to the SQL Server instance in which you have saved your database.
- In the Object Explorer, right-click Databases.
- Click Restore Databases next.
- In the resulting Restore Databases window, click the General page (it is open by default).
- Under the Source section, you can choose any of the two options.
- You may choose a database from the dropdown list that you want to restore.
- Alternatively, you can select the device option and find your backup file by clicking the three dots (…).
- Since we have to restore the database from a backup file, we will go with the device option.
- On clicking the ellipses (…) in the Device option, the Select backup devices option will pop up.
- In the dropdown next to Backup media type, choose File and then click Add.
- Find the backup (.bak) file from the Backup folder you want to restore. It will be in a Backup. subfolder of the parent Microsoft SQL Server folder in your system drive.
- Select the backup file (.bak) and click OK.
- You will see the name of the database that you wish to restore in the Database field.
- The selected backup file will be visible in the Backup sets to restore section.
- In the left panel of the Restore Database window, click Options.
- Under Restore Options, select the option 'Overwrite the existing database (WITH REPLACE)'. This will overwrite your existing database. To avoid overwriting, create a new database and shift the physical file to a new destination.
- In the dropdown list next to Recovery state, select RESTORE WITH RECOVERY, to see the message, 'Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored'.
In case of differential or log restore after doing a full restore, select RESTORE WITH NORECOVERY. - Click the OK button to restore the database successfully. A message box will popup after the successful database restore, as shown in the image below.
- Click OK to complete the process.
As you can see in the image above, the database name in the example is SQL_TEST_DB, and we have restored it through full backup.
2. Restore the database by using T-SQL
If you don’t mind going the core technical way, the option to use T-SQL is also available. Here is how you can restore the database from the .bak file with the help of T-SQL commands.
- Open SSMS and connect with the SQL Server instance.
- From the toolbar at the top, click New Query.
- In the resulting Query Editor window, run the restore command as given below to restore a complete database from the backup file.
USE [master] BACKUP DATABASE [testing] RESTORE DATABASE [SQL_TEST_DB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TST_DB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 GO
3. Restore the database by using the Windows PowerShell command
Here is the Restore SQL Database command to execute this task in PowerShell by using the backup file.
Restore-SqlDatabase -ServerInstance [server-instance] -Database [database-name] -BackupFile [backup-file] -ReplaceDatabase
The command supports all kinds of back restores in SQL including transaction log restore, database file restore, and full database restore.
How to restore data from a corrupted backup file?
While databases are likely to go corrupt, backup files are equally likely to become damaged or corrupt because of various factors. These may include issues in RAM, hard disk, CPU, or network. Malware intrusion or sudden power outages can also cause corruption in the backup file, the same as in the case of a damaged database. You may use T-SQL commands in SQL Server Management Studio to recover corrupted backup files.
To proceed with this method, you need to verify the current state of your backup at first. The use of the VERIFYONLY option with the RESTORE command can help in this regard:
DECLARE @BackupPath NVARCHAR(255) = 'f:\backups\testdb.bak'; RESTORE VERIFYONLY FROM DISK = @BackupPath;
This will verify if your backup is healthy or corrupted. In the case of a corrupted file, it is better to use a different backup file to restore the database. In case of an obsolete or no recent alternate backup at all, you will have to restore and repair the corrupted database.
RESTORE DATABASE testdb FROM DISK = 'C:\backups\ testdb.bak' WITH REPAIR;
Restoring backup files - Things to consider
Before restoring a backup file, it is crucial to know that you cannot restore a .Bak file created in a newer SQL Server version to a previous version. If you still want to go the reverse way, you will have to install a server with a similar version and then proceed with the restoration of the backup file.
Here are some critical points to take care of before moving ahead with the backup file restoration:
- Make sure to close all active connections to avoid failure of the restoration process
- As already discussed, you cannot restore backups created on new SQL Server versions to older versions.
- To be on the safe side, you should create a backup of the active transaction log file
- Manage metadata to ensure the availability of the database on the other server during the restoration
Alternative to the manual process of backup file restoration
If you do not wish to get stuck in the technical hassles, a comparably easier way to resolve this issue is to use a third-party tool. Software such as Stellar Repair for MS SQL can prove effective in recovering data from corrupted databases or backup files.
Additionally, you may try several other automated tools as well. In any case, make sure to go through the features of the software before selecting the ideal one. It should be able to recover the data from a corrupted database and it should not put a burden on your budget either.
Why back up your SQL Server databases?
Now that you are aware of the ways to restore your data from a corrupt database or a corrupt backup file, let’s understand the importance of taking frequent backups of your database. A foolproof backup and restore strategy is imperative to safeguard against the loss of data caused due to numerous failures. The strategy should include:
- Taking regular backups of your database.
- Running the restore procedures to test the current state of your backups.
- Storing backup files in a safe location to avert a catastrophic data loss.
A valid, recent, and healthy backup can restore your data from several failures, such as:
- Errors caused by users, like wrongly dropping a table.
- Failure of media.
- Failure of hardware, such as permanent server loss or a corrupted disk drive.
- A natural disaster.
Creating a database backup is equally essential to carry out routine administrative tasks, such as:
- Copying databases from one server to another.
- Database mirroring and archiving.
- Set up Always On availability groups.
Types of database backups
Knowing about the different types of backups makes it easier to perform the restoration task.
Differential backup
It relies on the most recent full backup of a complete or partial database. It also includes the backup of a set of filegroups (differential base) or data files, which consist of the data modified since its latest full database backup.
Full backup
This backup type includes the entire data in a particular database, files, or set of filegroups. It also consists of enough logs to allow data recovery.
Log backup
It represents a backup of transaction logs consisting of all log records whose backup was not created in a previous backup of log files. The log backup files should include the .TRN extension.
Conclusion
Proper functioning of SQL Server is essential for the smooth running of organizations. Any kind of corruption in the database or the backup file could however stall the entire working. It could lead to loss of business reputation, financial damage, and several other threatening consequences. To get away with these hassles, prompt data recovery from the corrupted database is inevitable.
One of the most practiced ways to serve this purpose is to restore the database from a valid, healthy, and recently created backup file. You may execute this database restoration task through the user-friendly SQL Server Management Studio (SSMS) GUI, T-SQL commands in SSMS, or by running PowerShell commands.
Nevertheless, there is an equally likely possibility that your backup file gets damaged, leading to further hassles. You can restore data from any such corrupted backup file through the VERIFYONLY option with the RESTORE command. A comparably better alternative to these manual methods would be to confide in a third-party automated SQL recovery software.
It can help to restore data from a corrupt database quickly and without hassles. Stellar Repair for MS SQL is widely recommended software in this context. In the absence of a backup, you may face severe hassles in restoring the data. Therefore, taking regular backups of your database is crucial and a preferred move.
