Migrating SQL2K Database to another SQL2K Database

[multiplex77]

Hi,

Elementary question but i need help: I have designed and populated my SQL 2K database on my development server, and now I need to migrate it to another SQL 2K database (on my client's production server).

What is the easiest and fastest way to do this? i'm a bit confused with SQL 2K (I just started using it) cos previously with Access 2000, I could just copy and paste the .mdb file. But SQL doesn't have any equivalent of .mdb files which contain all the database info, right?

I've been reading that there are two main ways:
1. backup and restore
2. using sp_detach_db

Is backup and restore the easiest way? So can I just backup my database, burn the backup file on a CD ROM and go to my client's machine and Restore it from the CD ROM?

I've read up about sp_detach_db and it sounds very complicated.

I have this ONE MAIN CONSTRAINT:

My client is not willing to give me administrator access to their SQL 2000 server, so I can have to give her instructions and a CD ROM to to install/copy the database on her database server, so it can't be too complicated a process.

Please help! Thanks very much.

 

[wengyan]

Hi multiplex77

You can do it by SQL Server Enterprise Manager which will wizard you by GUI.
So you can detach a database and attach the database without knowing any SQL Statement,also you can backup the database and retore the database on other server easily. WENG YAN

 

[osjohnm]

I'm assuming that you are not on site at the client, so you would have to put it onto CD.

SQL Server uses files called .mdf for data and .ldf for the transaction logs.

The best thing would be to create a backup on the cd and restore the database off the backup and specify the 'force restore over existing database' if it already exists on the production server. Just right click on the server in EM and go all tasks...restore database.

If you don't want to overwrite the data in the production server with 'dev' data you might have some problems.

If you don't want to overwrite the data, then restore it with another name or copy your mdf and ldf files to cd and attach them to the production server. Then you will have to script the changes to the existing tables and then load the relevant data.

You can also attach the files if its a new database as well.

See sp_attach_db in Books Online

John