Friday, May 14, 2010

Using sample databases with SQL Server 2008 R2 Nov-CTP

Often times you may want to test something simple and you don’t want to create a database of your own. You can use Microsoft relational database samples. These samples are available for most versions. Recently samples are not bundled with the servers and you may have to get them from online sources.

After downloading the samples you must attach them to the SQL Server as these are Data and Log files. This article shows you the steps involved for attaching a Northwind database to SQL Server 2008 R2 Nov-CTP, but the procedure is the same for other versions as well. Additionally you can also use sp_attach_db and sp_detach_db stored procedures to attach/detach the databases.

Note: Download the SQL Server 2000 Sample DBS as well as Microsoft Sample Databases from the CodePlex site here:


http://sqlserversamples.codeplex.com/#databases


For SQL 2005 (top right of the URL) and SQL Server 2008 R2 databases you go to this URL:


http://msftdbprodsamples.codeplex.com/releases/view/37109

Attaching a Northwind Database to SQL Server 2008 R2

We will attach a Northwind database from SQL Server 2000 samples to the SQL Server 2008 R2 Nov-CTP


Download the SQL Server 2000 Northwind and Pubs databases ZIP file [SQL2000.Sample.Databases.(Northwind,pubs) zip file, 1.202MB]

http://code.msdn.microsoft.com/northwind/Release/ProjectReleases.aspx?ReleaseId=1401

Unzip to a folder of your choice.

Folder structure is shown here:








Right click databases node in SQL Server 2008 R2 Nov-CTP's SSMS (SQL Server Management Studio) to display the following:


When you choose attach drop-down menu by clicking the menu item you will display the following


Click Add… in the top of the window and it will take you to the default location of your database files as shown.


If you did not unzip the files to this location you can also browse folders on your machine to locate them as shown.


Now click on Northwnd.mdf to highlight it and click OK

This brings in both the mdf [data]and ldf [log] files to the Attach windows as shown.

Click OK and after processing your request, Northwind database will appear in your server as shown.


For moving data from SQL Server 2000 to 2005 read this popular article here [0.5 million page views]