Copy MSSQL DB Between 2 servers

M0rtym0use · #1 (**permalink**) 06-23-2004, 04:45 AM

Hi,
i want to replace our current ms sql db server with another.

what is the best way to copy the data from one to another without losing logins, SP's etc?

MM

ADAM Web Design · #2 (**permalink**) 06-23-2004, 07:08 PM

The best way I've found to do it is to open up one of the databases in Enterprise Manager and Import Data from the old one to the new one.

I haven't done this in about 12 months so I can't remember if relationships or things like that need to be defined, but the data itself does correctly transfer.

M0rtym0use · #3 (**permalink**) 06-24-2004, 02:14 AM

ADAMWebDesign,
the big problem with that method is the indexes are not copied just data unless i have got this completely wrong is there a safer way to do it?

MM

swstyles · #4 (**permalink**) 06-25-2004, 03:35 PM

Before exporting/importing your data, build a sql script and be sure that all your tables/storedproc/views and indexing info is exported. Then use query analyzer to execute sql script on new server. Be sure to change user name to correspond with new sql server. After you run the sql script, you can then go back to enterprise manager and do an export/import and all your indexes,defaults etc will be saved.

M0rtym0use · #5 (**permalink**) 06-25-2004, 05:58 PM

swstyles,
thanks for that i will definately give that a go!

MM