May 8, 2006 at 10:12 am
We had a fire recently. We have now purchased new servers to move data over to. What is the best way to move all our sql data over. I know how to move our created databases, but my questions/concerns lie with the system databases. Which ones need to get moved. I have read MSDB, Model, and Master. I have read master isn't necessary if you don't have created SPs, but we do. Is there a specific order to migrate? Any help is greatly appreciated.
May 8, 2006 at 10:16 am
The fastest way is stop SQL server service and copy out the system db's and then stop the service on the new server and copy the files over the existing db's.
Better make a backup of the databases before doing that and make sure that the new SQL server has the same Servicepack level and the same hotfixes applied as the old one.
regards,
Holger
May 8, 2006 at 10:21 am
Is there anything special though to attach any of the database? I don't have to log on as a single user? I can just simply copy the data over? I have identical setups in regards to file locations, hotfixes etc.
Thanks for the help.
May 8, 2006 at 10:28 am
not if you stop the sql server service. Then just copy them over. But as I said before make a copy of the original ones before doing that.
After that you need to restart the SQL server service. Make sure that your user databases are in the same location as on the old server.
Best thing is you detach the user databases before making the copy of the master database and re-attach the user databases on the new server after the restart.
regards,
Holger
May 8, 2006 at 10:54 am
Great, thanks Holger
May 8, 2006 at 10:56 am
The original databases must either be detached, read-only, or shutdown the SQL Server service before copying. Database activity occurs in the buffer pool and gets copied back to the data files on disk sporadically. The data files on disk are incomplete and inconsistent as long as they are open by SQL Server. Meaning that your copies will probably be corrupt and unusable.
David Lathrop
DBA
WA Dept of Health
May 8, 2006 at 12:08 pm
There's a couple of great articles on this site about moving databases. Do a search or go to the article link.
The specific titles you want are:
Moving System Databases - A Checklist by Christoffer Hedgate
Quickly Moving Databases by Chris Kempster
Move Your Master by Steve Jones (there are a couple of other articles on moving databases by Steve - check them out)
-SQLBill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply