Copying Database from One Server to Another

We recently got a new SQL Server 2000. I'm not really a SQL/Network admin but I was tasked to migrate some of our databases in the SQLSVR7 to SQLSVR2K.
I tried using DTS EXPORT but getting errors. Is there a better way to do this?
Any info would be appreciated.Doing an sp_detach_db on your SQL7 box followed by an sp_attach_db on your SQL2000 box is undoubtedly the easiest & quickest way to migrate your DB, but you will end up with orphaned users in your SQL2000 DB if there were an users in the DB (other than the dbo user, although even that can get orphaned). The orphaned users can be fixed with sp_change_users_login.

Another easy way to do it is by backing up your SQL7 DB and then restoring that backup file to a DB on your SQL2000 box. But, once again, will probably result in orphaned users again.

The reason these two methods work is because both the restore & the sp_attach_db will "convert" the DB format, if necessary, as they go (this will only work for SQL7 to SQL2000 boxes, SQL65 upwards has to go through a DTS package). At least from memory that's how it works (it's been over 3 years since I did it).

DTS is another quite reasonable option - all the DB upgrade wizard does is create a DTS package and run it. The DTS package (once again from a 3+ year old memory) just has a single "copy database" object, or something like that. Personally, I'm not a great DTS fan - I'm more of a T-SQL purist.

Hope that helps.|||You may have to script all of the database, and bcp in/out the data.

This is very timely.

As for the DTS did you click Copy Objects and data betweens SQL Server database?

Lystra|||Another easy way to do it is by backing up your SQL7 DB and then restoring that backup file to a DB on your SQL2000 box. But, once again, will probably result in orphaned users again.

I tried the RESTORE DATABASE option. I copied a complete backup from the SQL7 into a directory of the SQL2K. When I initiate the restore, I get a "Device activation error" (pls see attachment) and is asking me to use "WITH MOVE".

TIF|||You may have to script all of the database, and bcp in/out the data.

This is very timely.

As for the DTS did you click Copy Objects and data betweens SQL Server database?

Lystra

TFYI

What do you mean by "script all of the database"? How can I do that?

Yes, I have that option on a the DTS. It gets an error message pointing to another database (with no owner when I run the sp_helpdp) and the DTS is never completed. I get all the tables but I'm missing the views and all sprocs.|||If you have a skim through SQL Books Online you'll see how to do the restore properly (with the MOVE & REPLACE options). To help you out a bit I just whipped up this SQL batch for you to explain what to do. (I haven't actually executed this batch so it might contain syntax errors (typos) but I can't see any just glancing at it.)

-- Create the DB
create database MySQL2000DB
on
(
name = 'MySQL2000DB_Data',
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Data.mdf',
maxsize = 100MB,
filegrowth = 25MB
)
log on
(
name = 'MySQL2000DB_Log',
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Log.ldf',
maxsize = 50MB,
filegrowth = 10MB
);

-- Restore from the SQL7 backup
restore database MySQL2000DB from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\MySQL7DB.BAK' with
replace,
move 'MySQL7DB_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Data.mdf',
move 'MySQL7DB_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySQL2000DB_Log.ldf',
recovery;

-- Change the logical file names
use MySQL2000DB;
alter database MySQL2000DB modify file (name = 'MySQL7DB_Data', newname = 'MySQL2000DB_Data');
alter database MySQL2000DB modify file (name = 'MySQL7DB_Log', newname = 'MySQL2000DB_Log');

-- Fix up the orpaned users as reported from sp_change_users_login
exec sp_change_users_login 'report';
go

After you get the list of orphaned users you need to run exec sp_change_users_login 'auto_fix', '<username>' in the MySQL2000DB for each of those users listed when you ran sp_change_users_login 'report' where the '<username>' is the bit you change for each user (not sure how obvious that was to you).

Cheers,|||Thanks for the script. Although I was not successful when I run it, it did point me to the right direction that resolved my problem.

So basically, I used the RESTORE DATABASE tool of the SQL Enterprise Manager. However, I was missing the part where I need to change the path within the physical file name.

Thanks again!