Coding Horror: Sample Databases in SQL Server 2005

November 02, 2005

Sample Databases in SQL Server 2005

SQL Server 2005 doesn't include the classic Pubs and Northwind databases. You can, however, download them from Microsoft. You'll get both binary database images (*.mdf and *.ldf) as well as SQL scripts.

If you plan to use the binary database files (*.mdf and *ldf), first copy those files to the correct default location for database files in a typical SQL Server 2005 installation:

c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

This path varies somewhat depending how many instances of SQL Server you have on your machine.

You can either install from the Management Studio GUI if you have SQL Server 2005 Standard or Developer, or install from the command line if you have SQL Express. There is, however, a downloadable add-on version of the Management Studio GUI specifically for SQL Express.

Installing sample databases from the command line:

If you're using the binary files:

osql -E -Q "exec sp_attach_db @dbname=N'Northwind',
@filename1=N'C:\path\northwind.mdf',
@filename2=N'C:\path\northwind.ldf'"

If you're using SQL scripts:

sqlcmd -S .\SQLEXPRESS -E -I -i instpubs.sql

Installing sample databases from the Management Studio GUI:

If you're using the binary files:

Right-click the root database
Click "Attach"
Click the "Add" button and choose the *.mdf file from the \Data folder
Click OK

If you're using SQL scripts:

Click the "New Query" toolbar button
cut and paste the contents of the instnwnd.sql or instpubs.sql scripts in the query window
hit F5 to run.

There's also a rather large new sample database in SQL Server 2005 called AdventureWorks. Unfortunately, it's not installed by default. Rather than going through a tedious Add/Remove Programs cycle, you can download the standalone database from Microsoft. There's also a handy AdventureWorks schema diagram in HTML and Visio format.

Posted by Jeff Atwood View blog reactions

« GotDotNet: still sucking after all these years 64-bit Desktop vs. 64-bit Server »

Comments

Sorry to butt in, Jeff, but I think it is excellent that the sample databases, including AdventureWorks, are not installed now by default.
The samples pop up a lot even on our Production servers and having them not available will stop this happening.

marty kay on November 3, 2005 04:49 PM

I agree, but that's probably not a good default for the "Developer" or "Express" editions of SQL Server 2005..

Jeff Atwood on November 3, 2005 05:17 PM

Obviously we refer to "standard" sample databases a lot in the docs (at such times as we have to be specific). Pubs is out; for the time being (ASP.NET 2.0), we were pretty much stuck with Northwind. It's fine for illustrating most things, but I was brought up short one day when I was doing up some sample or other involving data binding the Hyperlink control and realized that there are no URLs in Northwind. Well, if that didn't say a little something about its age ...

mike on November 4, 2005 11:52 PM

Oh, and I'm ambivalent about including sample databases with Express stuff. Web Matrix used MSDE, which has no samples, and I never heard anyone complain. It was more interesting (and useful) to have people create their own database, actually. Just a thought.

mike on November 4, 2005 11:54 PM

very good information.

I'm in a problem using Webmatrix and MSDE. MSDE do not provide Northwind database with it..I' ve download Northwind.mdf and Northwind.ldf.But how will i configure it with MSDE+ Web matrix...Any one can provide me information

Mustafiz on December 23, 2005 06:06 PM

I prefer a prompt so I can decide, not Microsoft, regardless of what edition.

Northwind and Pubs should be on the sql 2005 disk

Steve on June 20, 2006 09:23 AM

thanks, i was looking for this information

aakash on August 14, 2006 05:31 AM

I can't attach the DB. Ran through the install process but AW didn't show up in the databases collection as I'd expected. Went through Attach... process but it wasn't in default location. Did file search and found TWO copies of the AW.mdf file. Both fail on attachment with following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server '[omitted]'. (Microsoft.SqlServer.Smo)

For help, click: [omitted]
------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: [omitted]

------------------------------

Any idea what this is about? I'm using Windows Authentication for my default connection.

Larry on October 23, 2006 07:17 AM

Is it possible that my SQL Server Management Studio is managing an SQL Server 2000 service? Next to the registered server in object explorer it lists the server name then "(SQL Server 8.0.760...)"

If this is the problem I can find no place where I can start or manage SQL Server 2005. Did I screw up the setup?

Larry on October 23, 2006 08:48 AM

Sorry all for the three posts but thought I'd finish up with results. It turns out that I had not, in fact, installed SQL Server 2005 DB Services. I also have SQL 2K and missed a check box somewhere, I guess. I redid the setup and this time the DB Svcs installed and I can access AW with no problem (after attaching).

I'm upgrading my instructional materials for 2005 and it is a nightmare when the professor can't even get it to work.

Larry on October 23, 2006 10:28 AM

I just had to install Northwind because some sample code I found required it. Microsoft should stop writing documentation that reference a database they no longer include with the product.

Paul Wu on March 23, 2007 11:57 AM

thanks dude, its valuable info.

koti on April 27, 2007 09:45 AM

Larry,

I have the same problem as you had to install AdventureWork database but I don't know what do you mean "installed SQL Server 2005 DB Services".
I wonder if you could be more specific about how to fix the problem.

I appreciate your help.

Lisa

Lisa on June 13, 2007 01:11 PM

Thank you for this quick help. It certainly helped me to quickly install Northwind. I can now easily connect to it inside Server Exploer in VS2005.

Vince on August 8, 2007 07:18 PM

Thank you for the info. Helped very much.

Vani on August 27, 2007 10:43 AM

FYI, the SQL Server sample databases have recently been moved to http://codeplex.com/SqlServerSamples.

Cheers,

J.C. Bize on August 29, 2007 10:31 AM

Your posting is very helpfull to find and install the sample databases on to a SQL Express 2005.

vs on September 12, 2007 06:02 PM

thank you

Le Hong Nguyen on October 16, 2007 09:55 PM

Really good info..it helped a lot to solve my problem

tvk on December 27, 2007 08:57 PM

Thanks very much for this info.

voodoomagic on January 28, 2008 03:49 PM

Thanks for this, really helpful!!!!!!

Sunny on February 11, 2008 12:28 PM

Thanks alot!!

andrew on February 21, 2008 02:01 PM

Great information. Many thanks.

Peter on March 29, 2008 08:21 AM