Menu & Search

Restoring a SQL Server Database

Restoring a SQL Server Database

This is a simple guide to restoring a full backup file in SQL Server, providing an overview of a few options that are available during the process.

It is always a good idea to read through Microsoft’s documentation on database restores before proceeding. This will ensure that you have the most up-to-date and accurate information.

How to Restore a SQL Server Database

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Right-click on the “Databases” folder and select “Restore Database”.

Restore Database SQL Server Management Studio

3. In the next window, select “Device” and then click on the three dots (…) to add a backup file.

Restore Database SSMS Add Device

4. Navigate to your database backup file and click “OK”.

SQL Server Restore Database Locate Backup

5. You can change the name of the database in the “Database” field. Once you have done that, click on the “Files” tab on the left-hand menu.

SQL Server Restore Database Choose Backup File

6. On the “Files” tab, you can define the locations for your data (.mdf) and log (.ldf) files. You can also use the “Relocate all files to folder” function to quickly relocate multiple files.

SQL Server Restore Database Backup Files

7. On the “Options” tab, you can choose the recovery state of your database. If you don’t have any other backups to restore, you can select “WITH RECOVERY” to make the database ready for use as soon as the restore is complete.

SQL Server Restore Database Options

9. Once you have configured the options for your restore, you can generate a TSQL script by clicking on the “Script” button at the top of the window.

SQL Server Restore Database Generate Script


SQL Server Restore Database TSQL

10. Instead of clicking “OK” on the wizard window, hit “F5” to run the script and restore the database.

Restore Database in SQL Server Successful

11. Once the restore is complete, refresh the database view in Object Explorer to see the restored Adventureworks database.

Restored Database in SQL Server