There are many benefits of sample databases, for example, it can be use for learning or studying database-related subjects, or use to test your script/query, etc. In those previous versions of SQL Server, Northwind and Pubs are popular sample databases, however, they aren’t maintain any more (they don’t update). Therefore, it is better and recommend to use more recent and up-to-date sample database for SQL Server 2012 “Adventure Works”. You can download at CodePlex –
Adventure Works for SQL Server 2012.
On this post, you will see step-by-step guide how to attach/install sample database Adventure Works on SQL Server 2012 Express using SQL Management Studio.
Step-by-step to install sample database Adventure Works
- Download “Adventure Works” sample database from CodePlex at Adventure Works for SQL Server 2012 by select AdventureWorks2012 Data File. The downloaded file will be .MDF extension.
- Place the downloaded file (the MDF file) to your database folder. In this example, I keep it in C:\databases.
Note: The default location for storing data and log files of SQL Server 2012 Express (x64) is C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA. For more information, see View or Change the Default Locations for Data and Log Files (SQL Server Management Studio).
- Open SQL Server Management Studio as Administrator.
- Connect to the SQL Server and click on New Query.
- Copy the script below and modify the path to the MDF file as according to your environment and click Execute.
CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'C:\Databases\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG;
- If you receive the following message while execute above query, it means that you didn’t run SQL Server Management Studio as Administrator. You are required to run SQL Server Management Studio as Administrator, check step 3 again.
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Databases\AdventureWorks2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
- You may see a message similar as below. This is not error, just a notice message. Try to refresh the database and you will see your sample database “AdventureWorks2012”.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect. New log file 'C:\Databases\AdventureWorks2012_Data_log.ldf' was created. Converting database 'AdventureWorks2012' from version 705 to the current version 706. Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.