If a database has turned corrupt or a disaster has occurred, leading to data loss, you can restore your database from an updated backup (.bak) file. This article outlines prerequisites for restoring SQL database backups and describes the steps to perform a database restore process.
Prerequisites
Before starting the restore process, ensure to fulfill these prerequisites:
- You will need SQL Server Management Studio (SSMS) installed on your system.
- Ensure you have sufficient privileges on the database you need to restore. Check the Permissions section for further details.
- Test the database backup before restoring it to the production server.
- Check if a valid transaction log file is available for the database.
Steps to Restore a Database on SQL Server
Follow these steps to restore a SQL database:
- Launch SSMS and enter details to connect to your SQL Server instance.
- From the ‘Object Explorer’ pane, right-click on Databases, then click Restore Database. This will open a ‘Restore Database’ window.
- In the ‘General Settings’ page, choose the Device option under the ‘Source’ section, then click the browse button (…).
- Hit the Add button in the ‘Select backup devices’ screen.
- Find and select the backup files you want to restore. After selecting the files, click OK.
Note: If you have multiple copies of database backup, use the most recent Full backup, the latest Differential backup, and consecutive Log backups to restore the database.
- Click OK again.
- The backup file’s name will be added in the ‘Database’ textbox, and the backup file will be listed in the ‘Backup sets to restore:’ section.
Note:
- If you want to relocate the backup files to another folder, click the Files option from the ‘Select a page’ pane.
- In the ‘Files’ page, choose the ‘Relocate all files to folder’ checkbox.
- Select the Data file folder and Log folder to restore the db files.
- Optionally, click Options in the ‘Select a page’ pane to choose advanced options for restoring the database. From the Options page, under the Recovery state box, select an appropriate choice to define the database state after the restore process.
- RESTORE WITH RECOVERY: This default option rolls back any uncommitted transactions to make the database ready for use. However, you cannot restore additional transaction logs. Choose this option to restore all of the backups and bring the database online.
- RESTORE WITH NORECOVERY: Selecting this option doesn’t roll back any uncommitted transactions, thereby leaving the database in restoring state. And so, the database remains inaccessible until it’s recovered. You can restore additional transaction logs by choosing this option.
- RESTORE WITH STANDBY: This option puts the database in read-only mode.
See the Microsoft guide on Restore Database (Options Page) for detailed information about other restore options.
- Click OK to complete the restoration process.
What If the Database Restore Operation Fails?
Sometimes, the restore process might fail when restoring a database from a backup. This may happen if there is an active connection or the backup has become damaged. So, ensure there is no active connection while performing the restore process. If this is not the case, likely the backup has turned corrupt. In that case, you will need to repair the database to bring it back online.
Running DBCC CHECKDB with an appropriate repair option can help fix corruption in the database. Usually, you might need to run CHECKDB with the ‘REPAIR_ALLOW_DATA_LOSS’ option resulting in data loss. A better alternative is to use a SQL repair tool like Stellar Repair for MS SQL to repair and restore your database with all its data intact.
Conclusion
This article discussed the basic steps to restore a database on SQL Server from a backup. It also explained advanced options you can choose to leave a database in ‘ready to use, ‘restoring’, or ‘read-only’ mode. The article also suggests an alternative solution to bring a database online when the restore operation fails. You can run a DBCC CHECKDB command with a repair option to fix corruption in the SQL database and restore it to its normal state. But the command may fail to repair a severely corrupted database and take a lot of time to complete. If you want an easier and quick solution to get your database back in its original form without any data loss, using a specialized SQL recovery tool can come in handy.
