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.

{mospagebreak title=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.

Fig.1: Detaching Northwind from the Server

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 FilesMicrosoft SQL ServerMSSQLDatanorthwnd.mdf
C:Program FilesMicrosoft SQL ServerMSSQLDatanorthwnd.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.

{mospagebreak title=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 FilesMicrosoft SQL ServerMSSQL.1MSSQLData. Hence you should paste the Northwind data files to this folder as shown here:

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData
northwnd.mdf
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData
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.

{mospagebreak title=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.

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.

18 thoughts on “Moving Data from SQL Server 2000 to SQL Server 2005

  1. If you started with SQL Server 6.5 like I did, you will find the SQL 2005 Server will knock your socks off. Microsoft has crystallized its many years of research and development in delivering this product which has many, many useful features.
    Moving data from SQL 2000 to SQL 2005 was quick and easy. Migration error is flagged out to a hyperlink so that you look no further. By the way, Adventure Works, the sample database shipped with SQL 2005 server must be attached. It is sitting in one the folders. Follow this procedure and you will enjoy the ‘Adventure’.
    Thanks for reading this tutorial and if you find it useful and enjoyable kindly let me know.

    Sincerely,

    J

  2. You may also need to adjust the compatibility setting (properties>Options) to SQL2k5 90 and the owner (properties>files) to a local SQL login. I followed the article’s directions but I was unable to open the database diagrams until I corrected the compatibility and ownership with the help of a call to Microsoft.

  3. Thank you for the kind comment. Data which has is moved to SQL 2005 has most of the objects, but to use the objects effectively some valid users must be present. Hence the steps you took are the recommended steps. Compatibility issues work in SQL 2005 in several places, Notification, Replication, etc The article does not cover all aspects of moving the data comprehensively, but just one aspect of it given the contraints of size and anchoring reader interest.

  4. hi Jayaram

    I have an expired SQL Server 2000 (SP4) Installation, with a a couple of
    databases.

    I’ve since discovered that all I really need is SQL Server 2005 Express.

    How can I upgrade from the expired evaluation version of SQL Server 2000
    to SQL Server 2005 Express ?

    I need to migrate the databases, and if at all possible have the new
    2005 instance configured the same such that I dont have to reconfigure
    any of the client apps.

    Any advice you could give would be greatly appreciated!

    Thanks in advance !!

    Ben

  5. Question:
    How can I upgrade from the expired evaluation version of SQL Server 2000 to SQL Server 2005 Express ?

    Answer:
    Although your program has expired your data must be on your machine. Down load and install the SQL 2005 Express edition (a fresh isntall). However you may look up the limitations of this free edition. This is only a teaser if are going into production. Microsoft expects (recommend) you to upgrade, although you are not obliged to.Once you install the SQL Server express you can attch the databases to the new instance. There is a tutorial at this site which shows how it is done.

    Hope this helps,

    Jay

  6. Ben:

    After you install the Express edition follow the same procedure as in thsi tutorial. You may also try the stroed procedures. Good luck.

    jay

  7. Hi Jay,

    Thanks for your tutorial on the migration process. It was indeed helpful.

    At the end of your post, you mentioned something about

    “Also, remember to take heed of Microsoft’s recommendations as to the new stored procedures while attaching to SQL 2005.”

    Where can I get more information about these stored procedures? I need to find out more on the effects of SP on my App after I migrate. Thank you.

  8. Hi, in your comment you say that you start with SQL 6.5 …
    Ok, I’m new in SQL server world and i need to update a SQL server 6.5 to 2005; for what i read, i need to pass first to 2000 and after to 2005. Do you know some kind of step-by-step tutorial, because I’m really new in this and I’m a little beat afraid of destroy data…

    I will do it in this weak, i hope… so if you can help it will be really good.

    Thanks for the attention :)

  9. Is there any reason you could not migrate a database from 2000 EE to 2005 SE? And if not, would the procedure still be basically the same?

  10. Hi Jay,
    I have a SQL 2000 server and want to move to SQL 2005. But the condition is i cannot dettach the databases nor take them offline as our website is connected to it. Let me know the best way out of it.

  11. Hi Everyone,
    I too am rather new to SQL as well. We are migrating everything from 2K to 2K5. The Tutorial on migrating the mdf’s and ldf’s was great. I thought that was the way it was done.
    Now I need to migrate all of my Users. They each have a UID and a PWD. I see there is an sp for this but is there a place to find out what the complete command would be?
    Thank you very much for your assistance.
    Have a great day!
    Chandler

[gp-comments width="770" linklove="off" ]