Sample Databases in SQL Server 2005

November 2, 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
34 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 4:49 AM

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 5:17 AM

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 AM

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 AM


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 6:06 AM

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 10:23 AM

thanks, i was looking for this information

aakash on August 14, 2006 6: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 8: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 9: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 11: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 12:57 PM

thanks dude, its valuable info.

koti on April 27, 2007 10: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 2:11 AM

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 8:18 AM

Thank you for the info. Helped very much.

Vani on August 27, 2007 11: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 11: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 7:02 AM

thank you

Le Hong Nguyen on October 16, 2007 10:55 AM

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

tvk on December 27, 2007 8:57 AM

Thanks very much for this info.

voodoomagic on January 28, 2008 3:49 AM

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

Sunny on February 11, 2008 12:28 PM

Thanks alot!!

andrew on February 21, 2008 2:01 AM

Great information. Many thanks.

Peter on March 29, 2008 9:21 AM

It would really be nice if Microsoft just zipped up a .sql file that was compatible with 2005.

It would be about 10 less clicks each for the 1 million developers doing this...

ecards on June 28, 2008 9:53 AM

I tried installing sql srever management studio after installing visual studio 2008 and got this regarding msxls . Although I already had msxml 6 parser installed on my xp.

I was able to fix this accidently by trying to repair msxml 6 installation from control panel as documented on the link below:

http://gandhirohan.blogspot.com/2008/08/error-installing-sql-server-management.html

dc on August 11, 2008 5:48 AM

sdfsdf

qweqwe on September 6, 2008 3:52 AM

Cool Beans, I've got Northwind installed from the SQL Script: instnwnd.sql and now can go my thing. Extremely easy, thanks...

Joe Gakenheimer on September 24, 2008 9:15 AM

Recently, I wanted to get some sample databases to play around with in a fresh 2008 install. In order to use the intellisense and other schnazzy features, I got the AdventureWorks DB. The process is less than straightforward, so this write-up may be of assistance:

http://codeslammer.wordpress.com/2008/10/05/sql-server-2008-sample-databases/

Scott Bateman on October 5, 2008 3:29 AM

I looked all over the internet and this is the only solution that worked! Thank you so much!

Laurie on October 30, 2008 1:12 PM

Great info. Timeless. Saved my day.

Thanks a lot.

ks on December 5, 2008 7:59 AM

Excellent piece of work Jeff. Please go to work at CodePlex and make this stuff a little easier to understand for the rest of us!! I ended up using the scripts using SQL Server Management Studio Express.

WP on January 13, 2009 12:43 PM

Thanks. Finally some solution that works. Simple backup/restore Northwind suggested on other websides doesn't work.I have SQL2000 and SQL2005 in my office and want the database sample that works for both.

Greg Glowacki on January 24, 2009 6:45 AM

everybody follow this link

http://blog.sqlauthority.com/2007/06/15/sql-server-2005-northwind-database-or-adventureworks-database-samples-databases-part-2/comment-page-3/#comment-51069

shekar on April 17, 2009 1:39 PM

Good Resource.

Ajay Sinha

Ajaykumar Sinha on June 9, 2009 5:08 AM

The comments to this entry are closed.