General discussion


Moving SQL 7 databases to SQL 2000

By JodyGilbert ·
This question is being posted by the TechRepublic staff. The response(s) from this post may be turned into an article on the TechRepublic site. This is a winner-take-all scenario and the winner will be the person who provides the best and/or most complete answer in our judgment. If there are multiple correct answers we will select the one that was posted first. In addition to receiving a generous amount of TechPoints, the winner will get a free piece of TechRepublic gear (e.g., a coffee mug, a desk flag, or another item sporting the TechRepublic logo). This competition ends at 11:59 PM EDT on Sunday, May 30. Good luck and let the best techie win!

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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Joseph Moore In reply to Moving SQL 7 databases to ...

I did this a while back, so maybe I have a shot t the desk flag!

Ok. Basically, run a DTS import package from the SQL Server 2k machine, connecting to the remote SQL 7.0 machine, and import the DB schema.

Now for some details.
Log into the 2K SQL server, and open SQL Enterprise Manager. Add the 7.0SQL server to the Enterprise Manager view, using the remote SA account info for authentication (SQL7.0 only did SQL Server accounts, not Windows accounts like SQL2K does).
Then go to the SQL2K DB server, expand Databases, and make a blank database on it. This is where you will import your SQL7.0 db into.
OK, then right-click the blank db, choose All Tasks -> Import Data to bring up the DTS Import/Export wizard.
You would choose the "SQL OLE DB Provider for SQL Server" as the connection type, and choose the SQL7.0 machine as where from to get the db. Put in your SA credentials, then choose the db to import. Click Next.
You then specify the SQL2K server as the target of the import. Put your local credentials in for local SQL2K authentication (probably stick with Windows Authentication if you are logged into the SQL2K box as the Administrator), and choose the blank db you made as the destination db.
You are gonna then choose what tables/views/stored procedures to import. Check everything (as I remember this is how this part works; you get to choose what to import).
Then just keep clicking Next, and choose to run the import Now.
Then you get the grinding gears window as the db is imported to SQL2K.

Collapse -

by Joseph Moore In reply to

Now, things to keep in mind when the import is done. Someone (a SQL developer) will need to check out the Stored Procedure syntax, as it is different between SQL7.0 and SQL2K. 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 SQL2K box, check your Logins to see who has rights to this newly imported db. Reset them to the new domain and user accounts accordingly, using Windows Authentication accounts whenever possible.

There you go. Do this for every db on SQL7.0 that needs to be in SQL2K.

Collapse -

by JodyGilbert In reply to
Collapse -

by JodyGilbert In reply to Moving SQL 7 databases to ...

This question was closed by the author

Related Discussions

Related Forums