July 20, 2011 at 4:42 am
Hi,
We have a need to move Terabyte databases to a new location, some of which are on clustered servers. As usual the business does not want any "down time". However, the time required to take a back up, verify, transport to new location and recover from back up is clearly going to be a lengthy process.
Has anybody encountered/resolved this issue before? Any thought, pointers etc would be most welcome.
Thank you
colin
July 20, 2011 at 9:09 am
detach-attach will be quicker than backup & restore.
July 20, 2011 at 9:24 am
Yes, I was thinking of detaching, copying the mdf and ldf files and then transporting these to the new location for reattaching to the new servers. Hopefully the copies wil be fine to attach?
Will need to script out logins, jobs etc in the normal way and restore these to the new Instance. sp_help_revlogin usually works, but not always in 2005. 🙁
July 20, 2011 at 10:08 am
You can do that in multiple steps to.
Take full backup, copy to new location and restore (norecovery).
Then the day before the move, take a diff and move / restore.
During the day do hourly log backups and keep restoring.
Then once you're ready for the move, take a tail log backup (puts the db in norecovery mode so transactions stop on that server).
Ship, restore and start up.
Ideally you'll want to run a checkdb to the new server once so that you know you have a good db. Using this your downtown will be minimal. It can't really be 0 but that'll be close.
July 20, 2011 at 10:11 am
I would go for the backup/restore method outlined above over the dettach/attach as this way you can have minimal downtime.
July 20, 2011 at 10:14 am
Thanks folks. A plan is starting to come together
July 20, 2011 at 10:18 am
steveb. (7/20/2011)
I would go for the backup/restore method outlined above over the dettach/attach as this way you can have minimal downtime.
Also if you move the db and even only 1 sector is bad on the new location, you're pretty much dead to recover from backup anyways.
Moving is much more dangerous than copying for that very reason.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply