Data Management

Move SQL 7 databases to SQL Server 2000

What's the easiest and safest way to move SQL Server 7.0 databases from one domain to a SQL Server 2000 box in another domain? TechRepublic member Joseph Moore has the answer.

Submitted by: Joseph Moore


Problem

An organization's SQL 7 server resides in one domain and holds a number of databases. The IT department now has to move those databases to a SQL 2000 server in another domain. What is the most foolproof and efficient way to accomplish the move?

Solution

DTS import

Run a DTS import package from the SQL Server 2000 machine, connecting to the remote SQL 7.0 machine, and then import the DB schema. Here are the steps to follow:

1. Log in to the SQL Server 2000 machine and open SQL Enterprise Manager.

2. Add the SQL Server 7.0 machine to the Enterprise Manager view using the remote SA account info for authentication. (SQL 7.0 does only SQL Server accounts, not Windows accounts as SQL 2K does.)

3. Go to the SQL 2K database server, expand Databases, and create a blank database on it. You'll import your SQL 7.0 DB into this database.

4. Right-click on the blank database and choose All Tasks | Import Data to bring up the DTS Import/Export Wizard.

5. Choose SQL OLE DB Provider For SQL Server as the connection type, and choose the SQL 7.0 machine as the source of the database.

6. Enter your SA credentials, choose the database to import, and click Next.

7. Specify the SQL 2K server as the target of the import, and enter your credentials for local SQL 2K authentication. (You'll probably want to stick with Windows Authentication if you are logged in to the SQL 2K box as the Administrator.) Choose your blank database as the destination.

8. Choose which tables/views/stored procedures to import. Just keep clicking Next, then choose Now to run the import. You should get the grinding gears window as the database is imported to SQL 2K.

Additional considerations

Here are a few things to keep in mind when the import is complete. Someone (a SQL developer) will need to check out the stored procedure syntax, because it is different between SQL 7.0 and SQL 2K. Some syntax will need to be upgraded. The SQL Server Books Online will help with syntax changes that need to be made.

Also, on the SQL 2K box, check your Logins to see who has rights to this newly imported database. Reset the rights to the new domain and user accounts accordingly, using Windows Authentication accounts whenever possible.

Next steps: Build your skills with these hand-picked resources
> SQL Server 2000 Books Online
> SQL Server 2000 Resource Kit
> ExtremeExperts SQL articles
> MSDN Library: Microsoft SQL Server
> Experts-Exchange: Microsoft SQL Server Resource Channel
0 comments

Editor's Picks