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).
- Download the WideWorldImporters-Full.bak file if you're using the Evaluation, Developer, or the Enterprise Edition of SQL Server.
- Or download the WideWorldImporters-Standard.bak file if you're using SQL Server Standard Edition.
-
Launch the Restore Database Dialog Box
In the Object Explorer, right-click on the Databases node and select Restore Database... from the contextual menu.
-
Select the Backup File
Under the Source heading, select Device and click the ellipses button (...) to launch the Select backup devices dialog box.
-
Select the Backup File
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.
-
Check the Settings
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.
-
Success Message
You will receive a message informing you that the database was successfully restored.
Click OK to finish.
-
Check the Database
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 perform the same database restore as above using SQL.
To do this, open a new query window and execute a
RESTOREstatement.The
RESTOREstatement accepts various options (just like the GUI option), but you can also run a simple restore with a minimum of code.Sample Code
Below is an example of a simple restore script that specifies the database file to restore, and the destination database.
It also specifies where the data and log files will be located.
After running this code, the WideWorldImporters database will be created.
Delete the existing database first by running the following code:
You can also delete a database via Object Explorer by right-clicking on the database name and selecting Delete from the contextual menu.
Now check that the database is no longer listed in the Object Explorer. You might have to right-click on Databases and click Refresh before it disappears.
Once you've confirmed that the database no longer exists on your server, run the following script to bring it back:
Display a Progress Message
You can use the
STATSoption to receive an update on the progress of the restore process.For example,
STATS=10will result in 10 percent processed, 20 percent processed, etc as the database is being restored.You can see the full syntax for the
RESTOREstatement at the Microsoft website.
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.