How can I copy a sql server 2000 database to another site ?

[chu2654]

How can I copy a sql server 2000 database to another site ?

 

[SQLDenis]

1 back and restore
2 detach and attach
3 move all tables and data with DTS
4 Script out all the tables BCP out and BCP in all data

1 and 2 are the easiest

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[johnherman]

Also
5 replication
6 linked servers and INSERT...SELECT cross platform SQL.

As Denis noted, 1 and 2 are easiest.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[chu2654]

Thanks for reply. I backup the database to a test.mdf file. Then, when I try to restore it, how can I load the test.mdf. I use backup in sql enterprise manager, but I can't find the function to load the file.
What's detach and attach ?

 

[gmmastros]

Here's how I usually do it.

1. Open Enterprise Manager
2. Drill down to the database you want to copy
3. Right click on the database
4. Select properties
5. Click the data files tab
6. Write down the 'location' for the data files.
7. Click the Transaction Log tab
8. Write down the 'location' for the transaction log files.
9. Click CANCEL to close the window.
10. Right Click the database again.
11. Click 'All Tasks' -> 'Take Off Line'
12. Open Windows Explorer
13. Copy the files you wrote down (in steps 6 and 8) to the new computer.
14. On the original computer, right click the database, then 'All Tasks' -> 'Bring On-Line'

On the new computer....
1. Open Enterprise Manager
2. Open the server group
3. Open the server name.
4. Right click on Database
5. Click 'All Tasks' -> Attach
6. Select the MDF file (that you recently copied to the new server).
7. You may want to change the database owner.
8. Click OK.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom