One of the first things I did when I began my journey with SQL Server was installing a sample database. Though it seems to be very simple now, but not really when I just started leaning database. So, this post will be helpful for friends wanting to start their career in SQL Server or as a junior DBA.
Even though there exists quite a few sample databases like AdventureWorks, Northwind, Pubs..etc, in this post I will focuss only on AdventureWorks, since starting SQL Server 2008, Microsoft discontinued support for the Northwind and pubs sample databases. Moreover, AdventureWorks is the Microsoft’s official sample database for later versions of SQL Server.
   1. Where to look for downloading SQL Server sample databases
Microsoft’s CodePlex.com is the start to download official sample databases. Browse to http://sqlserversamples.codeplex.com and look/search for your desired sample database OR alternatively you can directly navigate to http://msftdbprodsamples.codeplex.com for AdventureWorks sample database.
   2. How to find the “right” AdventureWorks sample database
If you are a newbie, this can be really confusing. There are sample databases for different environments and various versions. You can chose a download based on:
- Version (2012, 2008R2, 2008)
- Environment (OLTP, DW, Azure)
- Full or LT (LT-Light version is a simplified and smaller sample database)
Below are the direct download links from CodePlex.com. You can either download a zip file (recommended for beginners) or Data file by selecting the “right” link below.
Click on your choice of AdventureWorks and save the download.
OLTP Environment – Zip file (contains both data and log files) |
AdventureWorks2012_Database.zip |
AdventureWorksLT2012_Database.zip |
AdventureWorks2008R2_Database.zip |
AdventureWorksLT2008R2_Database.zip |
AdventureWorks2008_Database.zip |
AdventureWorksLT2008_Database.zip |
OLTP Environment – Data Files (contains only data file) |
AdventureWorks2012 Data File |
AdventureWorksLT2012_Data |
AdventureWorks2008R2 Data File |
AdventureWorks 2008R2 LT Data File |
DW Environment – Data Files (contains only data file) |
AdventureWorksDW2012 Data File |
AdventureWorksDW2008R2 Data File |
Azure Environment Zip file (contains both data and log files) |
AdventureWorks2012ForWindowsAzureSQLDatabase.zip |
AdventureWorks2008R2AZ.zip |
   3. How to attach the AdventureWorks sample database
This varies a little depending on your download file type. If you have either downloaded
- Zip file (contains both data and log file) OR
- Data file (contains only data file)
I have seen people having issues due to the download type. So, I will cover both here.
Attaching a database using T-SQL script:
If using zip file download (contains both data and log file)
Use Master GO CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'C:\SampleDB\AdventureWorks2012_Data.mdf'), -- Data file path (FILENAME = 'C:\SampleDB\AdventureWorks2012_Log.ldf') -- Log file path FOR ATTACH;
If using Data file download (contains only Data file)
Use Master GO CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'C:\SampleDB\AdventureWorks2012_Data.mdf') -- Data file path FOR ATTACH;
Attaching a database using SSMS GUI:
- If you downloaded the Zip file, unzip the database, it should have data file and log file – move them to your desired location. Else, if you downloaded the Data file, nothing to unzip, just move the Data file to the desired location
- From the SSMS GUI, connect to SQL Server Instance where you want to attach this sample database
- Right click on “Databases” and select “Attach…”
- “Attach Databases” dialog window pops out –Â Click on the “Add” button
- “Locate Database Files” dialog window pops out – Locate the AdventureWorks data file and click “ok”
- If you are attaching from the zip file download which contains both data file and log file go to step 7. Else, if you are attaching from the Data file – select the log file and click “Remove”
- Click “ok” on the “Attach Databases” dialog window
- You can find the new AdventureWorks database attached under “Databases”
Below is the video tutorial on how to attach a database using SSMS GUI
Technical Reviewer: Jaipal Vajrala
Thanks for finally writing about >SQL Server –
How to Download and Install the ‘right’ AdventureWorks Sample Database | SqlServerZest.com <Loved it!
It’s actually very difficult in this full of activity life to listen news on Television, so
I only use the web for that purpose, and get the latest information.
This article does not provide the information advertised. It only provides a long list of links to various samples database without any help in picking the right one for the reader’s needs.
http://downupfiles.com/file/05TY77
SQL log File…Download from here
Thank you. Worked for me for SQLServer Express 2012. I did have to try multiple versions before I got it to work though.
Extra advice: be sure to run Mgmt Studio as ‘Administrator’ in order to create a new DB. And you may have to copy the MDF file to a common folder, instead of reading it from a \download folder.
Please can someone help please I get this message? When I try and try and change permissions on my C drive it won’t let me for some reason.
File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf” may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorksDW2012’. CREATE DATABASE is aborted.
It really solved my problem. Thanks alot.
Keep it up.
[…] SQL Server – How to Download and Install the ‘right … – One of the first things I did when I began my journey with SQL Server was installing a sample database. Though it seems to be very simple now, but not really when I … […]
[…] SQL Server – How to Download and Install the ‘right … – 3. How to attach the AdventureWorks sample database. This varies a little depending on your download file type. If you have either downloaded. Zip file (contains both … […]
[…] SQL Server – How to Download and Install the ‘right … – One of the first things I did when I began my journey with SQL Server was installing a sample database. Though it seems to be very simple now, but not really when I … […]
Very helpful, thank you!!
I attached North Wind Database (which is the 2005 version of Adventure Works) to my SQL Server 2012 machine, I see the database in my 2012 instance but it does not show any table. I run my SSMS as an administrator, but still no avail. To the best of my knowledge you can attach a database to a higher version but not the vice versa (2008R2 database can be attached to 2012, SQL Server 2012 instances) so, I cant figure out why I am not able to see my table? can someone help me solve the puzzle please. Thank you!
I see nothing wrong, can you try downloading a fresh copy of North Wind database from the codeplex https://northwinddatabase.codeplex.com/ and try accessing again?
I have read some excellent stuff here. Certainly price
bookmarking for revisiting. I surprise how a lot attempt you set to create this kind of great informative website.
Thanks it worked for me after trying many things. Thanks again!!
I continuously get error messages when I am attaching the database it to SQL Management Studio. I am in need of the AdventureWorks LT database. The zip files takes us to GitHub and those files are not working