Shifting database on another server

[isha]

There are some databases in our SQL Server. I want to shift some databases to another server.
In my view i can do this in two ways.

1. Take backup of the databases to be shifted. Then create the database in the new server and restore the backup.

2. Generate script of the database to be shifted and run it on the new server.

Which is the best out of the two? Is there another way to do this?

 

[Jamfool]

Backup and Restore is best of your two options as it will be quicker.

The other alt option is to detach the dbs, copy the files to the new server and reattach.

 

[Catadmin]

BTW, generating a script for a database is all well and good, but it only gives you the structure. It will not port over the data. So don't do that unless you only ever want an empty database.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mutley1]

I'd go with JamFool's 2nd suggestion - if you are allowed any downtime (and depending on size), detach the db, copy the MDF / LDF and attach on the new server.

Rgds,

M.