If you’re a long-time user of SQL Server and you’ve migrated to SQL Server 2005, you’d probably like to know where you can find the pubs database. Neither pubs nor Northwind is included by default when you install SQL Server 2005.

Even though my default installation of SQL Server is 2005, when I want a small and simple test bed to work with, I find that pubs or Northwind is preferable to using AdventureWorks. AdventureWorks is realistic: It has multiple schemas and much of the size and complexity of a live system. However, for some testing and certain kinds of demonstrations, a large, complicated database isn’t always optimal, and in those cases I fall back on pubs and Northwind.

The pubs database is bundled with Northwind on the Microsoft Download Center website, which says that you can use these sample databases with SQL Server 2005, SQL Server 2000 SP3 or later, SQL Server 2000 Desktop Engine (MSDE 2000) SP3 or later, and MSDE 2000 Release A.

After downloading SQL2000SampleDb.msi, double-click the file to run the Setup Wizard, which will extract pubs, Northwind, and a readme file into a folder named C:\SQL Server 2000 Sample Databases. The readme file contains instructions on how to run the installation scripts. The Wizard needs just 10MB on the C drive, but you might want to configure the database files (.mdf, .ndf, and .ldf) to reside in your regular \MSSQL\DATA folder, because these files can grow quite large. By default, the top-level folder is C:\Program Files\Microsoft SQL Server, but you don’t have to adhere to this architecture.

If you have SQL Server 2000 installed, you should be able to find these sample databases in \Program Files\Microsoft SQL Server\MSSQL\Data. You can simply detach the pubs and/or Northwind sample databases from SQL Server 2000 and reattach them to SQL Server 2005, or back up and restore them.

If you still have the SQL Server 2000 installation disks, you can find pubs and Northwind in the x86\DATA folder. Copy the files, paste them into a destination folder on your hard drive, and attach them using SQL Server Management Studio.

If you get a copy of pubs or Northwind from a source other than the Microsoft Download Center, check with your security administrator before installing either database, as they both contain a guest account that could be considered a breach of company security. If you obtain pubs or Northwind from the Download Center, the database won’t have a guest account.