Step 1
Moving Data from SQL Server 2000 to SQL Server 2005 - Taking data from SQL 2000 Server (Page 2 of 4 ) In order to detach a database, expand the Server node in SQL 2000 Server to reveal all the databases. Right click on the database you want to detach, in this case Northwind, and choose All Tasks. From drop-down menu click on Detach Database, as shown in Figure 1 below. Once you detach a database, that node disappears, but the related *.mdf and *.ndf remain where they are.

The Detach Database window opens as shown in Figure 2.

In the above window you can update statistics by placing a check mark. Clicking on OK detaches the database as shown Figure 3. The database files northwind.mdf (DATA) and northwind.ldf (Transaction Log) can be found at: C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.mdf C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.ldf

If you want to continue using the Northwind database you will have to attach again. However, since it is also required to move this database to SQL 2005 server, copy these two files before reattaching Northwind to SQL 2000 Server. Now right click on the Databases node in the Enterprise Manager and choose Attach Database, which opens up the next window, Attach Database - (local).


In this window you will need to look for the file you want to attach. It is important to know the file name and its location. It is also important to know who detached the database. Now click on the Verify... button.

This opens up a search window, Browse for Existing File-(Local), where you can search through your machine to locate the files as shown in the next figure.

In the above window you need to choose northwind.mdf. Both of the files will be attached as shown. The default name of the database node will be the same as the one that existed before detaching. After attaching the database you may carry out your operations with this database, as it becomes visible in the Enterprise Manager.

Step 2 Moving Data from SQL Server 2000 to SQL Server 2005 - Parking Data in SQL 2005 Server (Page 3 of 4 ) Step 1: You must paste the copies of northwind.mdf and northwnd.ldf files to a suitable location. SQL Server 2005 database files are found at: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Hence you should paste the Northwind data files to this folder as shown here: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ northwnd.mdf C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ northwnd.ldf Step 2:
Make sure the SQL Server 2005 is running. Right click the Databases node (currently there is no Northwind database) and click on Attach...as shown in the figure.

Step 3:
The Attach Databases window pops-up as shown. Click on the button Add....

Step 4:
This opens up the Locate Database Files - HODENTEK window. Expand the nodes as required and locate the *.mdf files. This window does not show the *ldf files.

Step 5:
Highlight northwnd.mdf and Click OK.

Step 2 Moving Data from SQL Server 2000 to SQL Server 2005 - Parking Data in SQL 2005 Server, continued (Page 4 of 4 ) The program goes through its motions and presto! The Northwind database is attached to the SQL 2005 Server. You will see the Northwind database objects are present by expanding the database node. Now if you open Windows Explorer you will be able to locate these files as shown. They will be found in the MSSQL.1 sub-folder. You need both the mdf and ldf files. If you fail to copy the .ldf file to the correct folder, or if it is missing you get the following screen. When you click OK to this screen, you will get the following message to let you know there was a problem. When you click OK you will be returned to the previous screen, and now a message is available as a hyperlink in the Messages column in the table under Databases to attach:. Attach in the message column. Clicking this link opens the MS SQL Server Management Studio message that gives more details about the failed attempt as shown. Summary It is very easy to attach/detach SQL Server 2000 databases to the SQL 2005 Server. It was noticed that the file sizes increased when attached to the SQL 2005 Server and provision must be made if very large databases are moved. The northwnd.mdf changed from 3.0 MB to 4.0 MB and the northwnd.ldf changed from 1.0 MB to 4.6 MB. Also, remember to take heed of Microsoft's recommendations as to the new stored procedures while attaching to SQL 2005.