What's the best way to copy a database?

[bigfoot]

I have SQL Server running here at work, and we just aquired an off site server. What is the best way to copy all my databases and all of my DTS scripts to the new box?

 

[MaffewW]

it depends. My preferred method is using the stored proc sp_attach_db, it is generally I think the quickest if you can allow your production server to be shut down. Is this something which will need to be done regularly. Does there need to be any replication between the 2?

Matt

Brighton, UK

 

[bigfoot]

No, this is a one time thing. The production server can be shut down for a bit while this is taking place.

I have 2 databases. One is local, and the other is for the web. The web one can be shut down at any time.
How do I detatch and retatch the databases? And how can I save the DTS scripts.

This is just to get the remote server loaded with our databases on a one time basis.

 

[mihaildamjan]

The eisiest way to detach database is in Enterprise Manager
right-click on the database you wanted detached,
click on the All Tasks, and there is Detach Database option.

To relocate DTS Package, double-klick on it to open it up,
klick on the Package, and select Save as. If the two servers are connected to the network you should be abel to select a different server to save this package to.
Or if there are too many packages to be relocated, go to
MSDB database and take a look at the sysdtspackages table.
By copying the content of that table to a different server you actually copy all the packages.
Of course, packages might have to be revised somewhat due to the fact the they have to run on the different server
name

Good luck

Mihail

 

[bigfoot]

Thank you so much.

 

[bigfoot]

The export wizzard will not show me the sysdtspackages to copy.