Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Moving Data from SQL Server 2000 to SQL Server 2005

Posted by Ravi Varma Thumati on May 8, 2009

Table of Contents:

  • Moving Data from SQL Server 2000 to SQL Server 2005
  • Taking data from SQL 2000 Server
  • Parking Data in SQL 2005 Server
  • Parking Data in SQL 2005 Server, continued

Moving Data from SQL Server 2000 to SQL Server 2005

This article explains how to move data out of SQL 2000 Server and into SQL Server 2005. This hands-on tutorial shows a step-by-step procedure for moving to SQL 2005, the new database from Microsoft released in November of 2005.

Introduction

There are times when you want to move, to upgrade your living quarters, for example. This analogy is not too bad; you move because you are cramped for space, you don’t have the latest fixtures, you take too long to shower because water is trickling and it is not hot. When administrators look at their servers and find that their file system has become cramped, they want to move to another file system, or another machine with more resources.

That’s when they start thinking of moving their data or entire database to another physical location. There are a couple of options for doing this, like copying, or backing up the data. There are stored procedures that help you do this, like the sp_attach_singlFile_db, sp_detach_db and sp_attach_db.  Microsoft recommends using variants that take advantage of the Create Database for attach procedure, as the older stored procedures will be discontinued in future versions.

In this tutorial the Northwind database on SQL 2000 Server is moved to SQL 2005 server using Enterprise Manager on SQL 2000 Server to detach the database and the SQL Server Management Studio to attach the database. The steps involved are described and illustrated with screen shots of relevant windows of interaction. SQL Server 2005 comes with its own sample database, but familiarity compelled me to move Northwind.

Taking data from SQL 2000 Server

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.

 move2000 to 2005_1

The Detach Database window opens as shown in Figure 2.

 move2000 to 2005_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

 move2000 to 2005_3

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).

move2000 to 2005_4 

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.

move2000 to 2005_5

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.

move2000 to 2005_6 

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.

move2000 to 2005_7

Parking Data in SQL 2005 Server

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.  

Parking Data in SQL 2005 Server, continued

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.

move2000 to 2005_8 

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.

move2000 to 2005_9

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.

move2000 to 2005_10 

When you click OK to this screen, you will get the following message to let you know there was a problem.

move2000 to 2005_11 

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.

move2000 to 2005_12 

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: