SQL Server 2016: Restore a Database

As with database backups, in SQL Server 2016, you can restore a database by using either Transact-SQL, PowerShell, or via the SQL Server Management Studio GUI.

Here, I'll demonstrate how to restore a database using the SQL Server Management System GUI, then using Transact-SQL.

Restore a Database via the GUI

In this example, we're going to restore a backup of a brand new database called WideWorldImporters.

The WideWorldImporters database is a sample database provided by Microsoft's SQL Server Team to demonstrate how SQL Server's features can be used in a real-world scenario.

This process will create a new database called WideWorldImporters. The database will contain various objects (such as tables, views, stored procedures, etc). It will also contain sample data, so there's no need to add your own.

Download the Database Backup File

Before you start, download the database (from GitHub).

  1. Launch the Restore Database Dialog Box

    Screenshot of restoring up a database in SQL Server 2016.

    In the Object Explorer, right-click on the Databases node and select Restore Database... from the contextual menu.

  2. Select the Backup File

    Screenshot of restoring up a database in SQL Server 2016.

    Under the Source heading, select Device and click the ellipses button (...) to launch the Select backup devices dialog box.

  3. Select the Backup File

    Screenshot of restoring up a database in SQL Server 2016.

    Ensuring that File is selected, click Add to browse to the database .bak file and add it.

    Once you've added the backup file and it's listed under Backup media:, click OK to exit this dialog box.

  4. Check the Settings

    Screenshot of restoring up a database in SQL Server 2016.

    Various fields in the Restore Database dialog box will be populated based on the backup file that you selected.

    Click OK to restore the database.

    You can (optionally) click Verify Backup Media to test that there's no problems with the backup file before running the restore process.

  5. Success Message

    Screenshot of restoring up a database in SQL Server 2016.

    You will receive a message informing you that the database was successfully restored.

    Click OK to finish.

  6. Check the Database

    Screenshot of restoring up a database in SQL Server 2016.

    Navigate to the WideWorldImporters database and expand its nodes to review it's various objects such as tables, views, stored procedures, etc.

    You can start working with this database immediately. For example, running SELECT * FROM Website.Suppliers; will return a list of suppliers using the Website.Suppliers view.

Restore a Database Using Transact-SQL

You can now use this database to try out different things. Feel free to expermiment with it. Query data, delete data, drop objects, anything. After all, if you completely mess it up, you can always restore the database from the backup file.