May 12, 2003 at 10:12 pm
I want to move over 150 production SQL 7 databases from one server to another each, having its own sql user. Whats the best way to do this ? Has any one seen or written any scripts that might help ?
May 13, 2003 at 1:55 am
Not done this personally but several ways I would do it.
Backup up all dbs on source and shutdown SQL on both source & dest. Copy all files db/master/msdb etc to dest. Restart SQL. Not pratical if server/dbs in constant use. Plus dest folder/files must match source.
Backup up all dbs on source and copy backups to dest and restore each db. I do this for occasional dbs where I need a copy of live db in dev.
Detach each db and copy files to dest and attach. Be careful that files are attached in the correct sequence. Again not pratical if dbs in constant use.
Hope this helps and I'm sure others may have better ideas.
Edited by - davidburrows on 05/13/2003 02:51:38 AM
Far away is close at hand in the images of elsewhere.
Anon.
May 13, 2003 at 4:09 am
A variation of detach/copy/attach works well. Trick is to make the new server have the same file locations as the original. Install SQL, put the master in the same location as original. Apply same service pack level. Stop the service on both machines. Copy files to new machine. Start service. Everything should be there. I wrote it up a little better a ways back:
http://www.sqlservercentral.com/columnists/awarren/20010425205439_1.asp
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply