Download And Restore Your Own Copy Of SQL Server AdventureWorks Database

Since many SQL Server tutorials and certification guides reference Microsoft’s sample database, AdventureWorks, I’ve decided to give an overview of how to download a copy of the database and restore it to your SQL Server environment.  Fortunately Microsoft provides a free copy of the database which can be downloaded from Microsoft Database Product Samples website.

Once you’ve downloaded it, unzip it and place the AdventureWorks2012.bak (or whichever version you downloaded) file on your desktop or some place familiar.

Launch SQL Management Studio and connect to your SQL Server.  Under the Object Explorer, right-click on Databases and select Restore Database…

AW_RestoreDatabaseX

On the Restore Database window click the radio button next to Device and then click the ellipses on the right.

restore_database_windowX

On the Select backup devices window make sure the Backup media type is st to File and then click the Add button to select the AdventureWorks2012.bak database backup file.  If you placed it on your desktop just navigate to your desktop path.  It is likely to be C:\Users\username\Desktop.

locate_backup_file_windowXJPG

Select the file and click OK.

Ensure that the full path of the backup file is displayed  in the Backup media window and click OK.

select_backup_device_fullX

On the Restore Database window, click on the Files page.  It is best practice to store your data files and log files on separate drives.  If this is not an option for you then just leave the default settings intact.  Otherwise, check the box next to Reallocate all files to folder and update the path in the Data file folder and Log file folder to point to their respective drives.  If you’re running SQL Server locally from a home server or PC you should, at the very least, create separate partitions for your SQL Server data files, log files and TempDB.

restore_database_files_windowX

Next, click on the Options page.  Unless you’re restoring the AdventureWorks2012 database over an existing database, you really don’t need to make any changes to this window.  However, if you are,  and you want a completely fresh copy of the database on your server check the box next to Overwrite the existing database (WITH REPLACE). It’s worth noting that WITH REPLACE should rarely be used for production databases as it overrides several safety checks that the restore process generally performs.

restore_database_options_windowX

Once you are ready to begin the restore click OK.

AdventureWorks2012 is a relatively small database and it shouldn’t take very long to restore.  Once the restore process is complete you will see a notification confirming that the process completed successfully.

restore_successful

If you receive an error, review the error and check each window for any invalid paths.  When dealing with multiple drives also be sure all available permissions have been granted.

You should now see the AdventureWorks2012 database listed in the Database drop-down in Management Studio.

The AdventureWorks database is an excellent sandbox for learning all aspects of a database from writing queries to modifying indexes and trigger to learning the ins and outs of SQL Server security.