How do I install a sample database using script?

When you are working with SQL Servers you may want to use database samples. Microsoft has published sample databases from time to time. Some of the databases published are,

Pubs
Northwind
Foodmart for OLAP
Adventure Works of various types.

These database samples comes in two forms; MDF & LDF files or script files which when run on the server installs the databases. The MDF and LDF files can be used to install the samples using either the Graphic User Interface (Right Click Databases node in the SQL Server Management Studio and choose Attach...) Attach menu item on SQL Server, or using T-SQL Scripts.

For SQL Server 2000 database files please follow this link: http://www.microsoft.com/enus/download/details.aspx?id=23654

For Adventure Works database files please follow this link: http://msftdbprodsamples.codeplex.com/releases

Make sure you get both the MDF and LDF files as both are needed while attaching the databases, read the following comments here, http://msftdbprodsamples.codeplex.com/workitem/19203.

For attaching the MDF / LDF files follow this link for step-by-step procedure here:

http://hodentek.blogspot.com/search?q=Sample+databases

Here the use of script file to install the sample on SQL Server 2012 is demonstrated. Note that the original documentation mentions that Northwind (2000) can only be installed on Windows 2003 and Windows XP, but you can install them on a Windows 7 machine.

From the link mentioned earlier for Northwind you can download the SQL2000SampleDb.msi file to a location of you choice.

Double click the MSI fileshown here,

Double click SQL2000SampleDb.msi in the download folder location to open

Click Next and agree to license terms on the next widow that is displayed. Click Next.

Click Next.

Click Next.

The database scripts as well as mdb / ldb files will be created in C:\SQLServer2000SampleDatabases as shown.

Connect to SQL Server 2012 and create an empty database Northwind

Click File | Open |  File...

The instnwnd.sql file opens in a query window. Check Syntax.

Click Execute.

You may get the message like:

Msg 2812, Level 16, State 62, Line 2

Could not find stored procedure 'sp_dboption'.

Msg 2812, Level 16, State 62, Line 3

Could not find stored procedure 'sp_dboption'.

Comment out the sp_dboption as shown as it is deprecated in SQL Server 2012.

--exec sp_dboption 'Northwind','trunc. log on chkpt.','true'

--exec sp_dboption 'Northwind','select into/bulkcopy','true'

Click Execute

The Northwind database will fully populated as shown:

Enjoy!

Mahalo