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.

1. Open SSMS and connect to your SQL Server.

2. Right click Databases and select Restore Database.

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

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

5. OK and OK!

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

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.

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.

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

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

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