How to Restore a Database on SQL Server?

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 (…).
restore database
  • Hit the Add button in the ‘Select backup devices’ screen.
add backup device
  • Find and select the backup files you want to restore. After selecting the files, click OK.
use database backup copy

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.
restore sql database

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.
sql database restored successfully

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Ad Blocker Detected!

We notice you're using an adblocker. We pay $25/month to keep this site running and serve 1 lakh readers a month – but we can't do that for free. We've made a point of never allowing auto-playing video ads or ads that obscure content, such as pop-ups. Please support us by whitelisting this domain.

How to disable? Refresh