DataGinger.com

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.

     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:

1. 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
2. From the SSMS GUI, connect to SQL Server Instance where you want to attach this sample database
3. Right click on “Databases” and select “Attach…”
4. “Attach Databases” dialog window pops out – Click on the “Add” button
5. “Locate Database Files” dialog window pops out – Locate the AdventureWorks data file and click “ok”
6. 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”
7. Click “ok” on the “Attach Databases” dialog window
8. 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

Read Full Post »