Upgrading from SQL 7 to SQL 2000 (Part 1)

By Mark Caldwell on 5 May 2002 | Tags: Installation

When you are ready to make the jump to SQL 2000, you will find that there are several ways that you can do this. Of course, each method has its advantages and disadvantages, and which one you choose will be based on a number of factors. I will attempt to introduce these factors and how they might sway your decision. Later I will walk through the process that I used just recently to accomplish this for a client with 25+ databases, all residing on the same server.

Introduction

First, a word of caution... As with any significant change to your SQL Server database, do a full backup before starting. And in this case, you might want to do an OS-level backup of the entire machine, not just a SQL Server database backup. Better safe than sorry, especially when sorry might also mean unemployed (or these days, especially for consultants, might mean being a defendant in a lawsuit).

Methods

The first decision you need to make is whether to upgrade the current server (option 1), or do a clean SQL 2000 install on a new machine (option 2). As with any system, doing an upgrade to the existing server eliminates most of the other decisions you have to make. But also like other systems, a clean SQL 2000 install on a new machine is, well, clean. If you have the resources, I encourage you to go with option 2. A clean install on a new machine is the easiest to recover from, if anything goes wrong. You just re-enable the old machine and you have lost nothing but a little time. An additional benefit is that you can easily do one or more "test runs" of the migration to make sure you have accounted for everything before you actually commit to the "production run".

If you have chosen option 2 (clean install, new server) then you have at least four options on HOW to migrate your databases from your old server to the new one*. You can a) Run the Copy Database Wizard that comes with SQL 2000, b) Run DTS with the Copy Objects and Data between SQL Servers, c) Backup and Restore, or d) Detach/Attach.

I have a distrust of wizards for anything of this potential magnitude, so I early on ruled out using the Copy Database Wizard and the DTS option because I wanted to know more specifically what was going on behind the scenes. This may just be superstition or paranoia on my part, but I learned a lot in the process of having to do this manually. Besides, a note on Microsoft’s MSDN web site about the Copy Database Wizard says it does the following:

The Database Copy Wizard is based on detach and attach functionality that allows user databases to be moved or copied from a source to a destination server. A Data Transformation Services (DTS) package performs the actual move or copy operation You can schedule the package to run at a specified time or rerun the package if required.

which is not that different from what I did manually anyway. The backup/restore option would work fine in most scenarios, but in my case I was moving 25 small databases and wanted to do them, as much as possible, in a batch. It seemed just as simple to script my detach/attach commands as it would be to script backup/restore commands. And since I’m copying files at the OS level between machines anyway, the backup/restore just seemed like an extra step and an extra level of complexity.

In contrast, If you are moving one large database instead of 25 small ones, you might wish to take a different approach. All of these methods should work just fine, as long as you move ALL parts that need to be moved.

What Has to be Moved

Regardless of which migration method you choose, you need to ensure that all of the following are migrated and/or updated:

Data and Log Files
Server Logins
Database users and their mappings to Server Logins
Full-Text Indexes (enable and repopulate on new server)
Other Indexes (rebuild/repopulate on new server)
Statistics (update on new server)
SQL Agent Jobs
DTS Packages

Some of the information listed above, like Jobs and Server Logins, is stored in the system databases. DO NOT try to migrate the four system databases (master, tempdb, msdb, and model), because these are so integral to the version that you are running, attempting to move them tends to lead to massive headaches. Instead, find ways to move the information so that it will be re-created in the new system databases.

For example, I used the Generate SQL Scripts option from Enterprise manager to script the SQL Agent Jobs that we had. Then I just opened Query Analyzer and ran the script to build them in the new server. Similarly, I used the procedure explained in Microsoft Knowledgebase Article Q246133 titled How to Transfer Logins and Passwords Between SQL Servers to extract the logins from the old server and recreate them in the new server, keeping the passwords encrypted during the move. Remember that, in any database move to a new server, your database users will get orphaned from the server logins, and you will need to realign these. Chadmat wrote a great script that is found in the SQLTeam Script Library to fix orphaned users. To learn more about orphaned users in SQL Server, read this article on Swynk.com:

In Part 2, I will walk through the exact steps I took to accomplish this task with code or links to scripts that I used.

* NOTE: If your databases are involved in replication, do some additional reading on MSDN and other sites to learn about possible side-effects of the methods discussed here. Also, from the Upgrading to SQL Server 2000 FAQ, "The only way to move a SQL Server 6.5 database to SQL Server 2000 is to run the SQL Server Upgrade Wizard."