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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA

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:pathnorthwind.mdf',
@filename2=N'C:pathnorthwind.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.