October 29, 2014 at 12:21 pm
Hi
I've been tasked with trying to figure out a method to move a database from it's current unstable storage, onto some new storage on the same server/instance, with minimal downtime. Simply reassigning the data and log files to the new drives cannot be done, as the time it would take to copy the data/log files to the new location would take it out of the tiny maintenance window I have!
OPTION 1
I was going to create a new SQL Server instance on the same server, restore a backup of the database to the desired new stable storage drives and then configure Mirroring, get the DB's synchronised, then reverse the roles. Then I could safely sever the mirroring link and bring the new principal on-line. Only caveat to this is that a new instance is required, so it's dependent on how easy the app servers are to change so that they point to the new instance.
Are there any other ways that this can be achieved?
October 29, 2014 at 2:59 pm
How large is the Data and Log files? And is there a period of time where the database won't be modified that would allow you to backup and restore it? If so my suggestion is to create a full backup of your database with compression (this will shorten the backup time), then restore it to the new location but under a different name.
So for example if your database MyStuff is on E:\Data\MyStuff.MDF and E:\Data\MyStuff.LDF but you want it on F: instead, back it up and restore it as MyStuffTMP and move the files to F:\Data\MyStuff2.MDF and F:\Data\MyStuff2.LDF. Then take MyStuffTMP offline, rename F:\Data\MyStuff2.MDF and F:\Data\MyStuff2.LDF to F:\Data\MyStuff.MDF and F:\Data\MyStuff.LDF. Your files are now moved and MyStuff has not been affected. Drop MyStuffTMP.
Now verify anything using MyStuff is shut down and take it offline, then run this:
ALTER DATABASE [MyStuff] MODIFY FILE ( NAME = MyStuff, FILENAME = 'F:\Data\MyStuff.MDF' );
ALTER DATABASE [MyStuff] MODIFY FILE ( NAME = MyStuff_log, FILENAME = 'F:\Data\MyStuff.LDF' );
And bring the database back online. It should now be pointing to the new location.
Would this work? You just need to verify nothing has written to the database while it's being backed-up and restored, but the downtime would be minimal. You could even re-attach the original database (on E: in my example) as MyStuffTMP and compare it back to MyStuff on the new location just to verify no data has changed. If any has you can copy it over if that's possible.
This is the only way I can think of to do this with the least amount of downtime.
HTH.
October 30, 2014 at 4:09 am
Thanks for the suggestion.
Unfortunately, this will not work for my scenario. The database from what I know is around 275GB in size, not sure of the log file size.
I have a 1 hour window, unfortunately the backup and restore operation cannot be achieved in this period of time 🙁
I was thinking of restoring a backup of the database to the same instance, under a different name though (is this possible without affecting the original DB?) - then I would sequentially apply transaction log backups that have been taken since the database backup? Would this work in theory?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply