I see all of these examples using the AdventureWorks SQL Server database, how can I get my own copy?
Microsoft provides a backup of the AdventureWorks2014 database on its CodePlex website. The 2014 version of the AdventureWorks databases can be found at the following link: Adventure Works 2014 Sample Databases. There are multiple zip files on this web page for both the OLTP AdventureWorks2014 database and the dimensional model for data warehousing named AdventureWorksDW2014.
In this tip, we will download the Adventure Works 2014 Full Database Backup.zip file as shown above. Unless you have changed your browser's settings, the zip file will download to your Downloads folder.
Once the download has finished, you have to extract the contents of the zip file. The zip file contains a single SQL Server backup file with a .bak extension named AdventureWorks2014.bak.
After extracting the file, copy the file to your database instance's Backup folder. You might get the following pop-up message, since I am the administrator on my own PC, I clicked on Continue.
In SQL Server Management Studio (SSMS), right-click on "Databases" and choose "Restore Database..." to bring up the Restore Database window.
In the Restore Database window, we have to specify the AdventureWorks2014.bak file that we have extracted and moved into the database instance's Backup folder. In the Source groupbox, click on the Device radio button and then click on the ellipsis to the right as shown below.
In the Select backup devices window, the Backup media type is File. We need to click on the Add button.
In the Locate Backup File window, navigate to your database instance's Backup folder. Select the appropriate backup file. As shown here AdventureWorks2014.bak and AdventureWorksDW2014.bak reside in the Backup folder. Click on AdventureWorks2014.bak and then click on OK. This will return you to the Select backup devices window.
In the Select backup devices window, the path and filename to AdventureWorks2014.bak should now appear. Click on OK to close the Select backup devices window and return to the Restore Database window.
In the Restore Database window, the database name will be populated. Click on OK to begin the restoration process. A progress bar will appear at the top of the window.
Upon successful completion, the following window will appear. Click on OK.
Go to the Object Explorer in SQL Server Management Studio and browse the objects in AdventureWorks2014. If the AdventureWorks2014 database does not appear, right-click on Databases and choose Refresh.
Similar steps can be followed to restore the AdventureWorksDW2014 database to your computer. Also, check out the following tips on database backup and restoration.
- How to restore a SQL Server backup
- Backup and Restore SQL Server databases with Azure cloud services and SMO
- Backup and Restore with Cloud services (Windows Azure Blob Storage Service) using PowerShell
- SQL Server Restore Tutorial
- All SQL Server Restore Tips
Last Update: 2016-02-17
About the author
View all my tips