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, covering a just a few options available during so.

As always, have a read through Microsoft’s documentation page before-hand for proper info.


How to Restore a SQL Server Database

1. Open SSMS and connect to your SQL Server.

2. Right click Databases and select Restore Database.

Restore Database SQL Server Management Studio

3. Select Device in the next window and the 3 dots.

Restore Database SSMS Add Device

4. Click Add to navigate to your backup file as shown above and then navigate to your database backup file.

SQL Server Restore Database Locate Backup

5. OK and OK!

SQL Server Restore Database Choose Backup File

6. You can amend the Database Name as shown above. Once done, click on the Files tab on the left-hand menu.

SQL Server Restore Database Backup Files

7. This is where you define the locations of your data (.mdf) and log (.ldf) You’ll see the original file information from when this backup was taken, and it’ll pick-up your default settings for your restore. The Relocate all files to folder function is useful if there’s many files.

SQL Server Restore Database Options

8. Onto the Options tab, the only thing I’ll mention in this post is the Recovery State as arrowed above. I don’t have any other backups to restore so WITH RECOVERY will make the database ready for use as soon as the restore has completed.

9. The restore is ready to go, but first we’ll have a look at the TSQL behind this restore. Hit the Script button at the top.

SQL Server Restore Database Generate Script
SQL Server Restore Database TSQL

10. Instead of clicking the OK button on the wizard window, hit F5 and run that script.

Restore Database in SQL Server Successful

11. We now have a restored AdventureWorks database. Refresh the database view within Object Explorer to see the treasure inside.

Restored Database in SQL Server

Hope this is useful. I’ll be expanding on this in future posts.