Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqllogi
Starting Member
4 Posts |
 Posted - 2010-12-30 : 19:36:18
|
| Hi, I need to move all databases to a new server. I am going to build it with the same hostname, hardware but just in a different domain. I had 2 ideas and wanted to get some thoughts on it.1) copy the physical database files with same path to the target server and restore the master database. Then, all the logins, db owners and databases will be attached2) bring sids over from source to target and do a detach/attach from old to new server.Anyone ever done this? Trying to script it out due to the amount of databases. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-30 : 22:33:33
|
| I would do it all with backup an restore - it means you will have the backup files to save and can test the process without taking down the source system.For system databases I would be tempted to script the logins and everything else (you can retain the sids) and apply them rather than restore - but that's because I think it's easier and safer.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-30 : 22:42:06
|
| I've used your option 1 numerous times in the past with great success. The only problem with it is the downtime, which is dependent on the size of the files and the network speed. I've never done it across domains, but I don't suspect that will be a problem. These days I use database mirroring to switch to a new server as it means just about a minute of downtime. I prep the server ahead of time by scripting everything out, then setup mirroring. Once we are ready to do the switcheroo, we shutdown the apps, I failover the databases, and then we bring up the apps. You can even rename the server in there to avoid having to redo the connection strings.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-31 : 10:35:57
|
| I would do it all with backup/restore as well. Script all of the backups and restores ahead of time.With the target server being in a different domain, none of the windows logins will be valid, so make sure you're prepared for that as well. |
|
|
|
sqllogi
Starting Member
4 Posts |
Posted - 2010-12-31 : 15:43:59
|
| Thanks for the replies. Good info! |
|
|
|
|
|
|
|
|
moving 100 db's to another server