March 16, 2009 at 2:29 pm
I have 60 databases ofcourse they are small in total they come around 600GB , Now I want to move them into another drive with less down time as much as possible , also i want to move them all at once, I dont want to dettach each db and attach each and i have to make sure i have attached all the databases after moving files on to another drive.
Guys.. suggest me the best option.
thanks
March 16, 2009 at 4:40 pm
The least-downtime way to do it for one database that we've found is to:
1. Put the database into full recovery if it's not already
2. Take a full backup
3. Restore the full backup with the NoRecovery option to a database on the new drive
4. (Set the old database to single-user if you can't eliminate users in any other way, then) Take a log backup and take the database offline
5. Restore the log backup to the new database
6. Rename the old database to something else
7. Rename the new database to the old name
(8. Change the recovery setting and multi-user setting on the moved database if necessary)
Of course, doing that for 60 databases is going to be somewhat painful, but you can do them individually or have multiple "migrations" running in parallel, so it's going to be very flexible.
If the master database is on a separate drive you might be able to use some tool to mirror the files on the old drive to the new drive (I don't know if there is such a tool, that can replicate open files in realtime) then at the change-point take all the databases offline, rename the drive so the new drive has the old drive's letter then bring the databases back online. I've never tried this but the theory should work. If master is on the old drive and you're not going to be migrating it you could stop SQL Server just before the change, change the startup parameters so it points to master on what the old drive's new letter is then restart it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply