SQLTeam.com | Weblogs | Forums

Script to run db Restores in parallell (same time)


#1

I am looking for something already done, that I can just modify. Yes I know, but I have a project with very little time and this script is a little out of my forte to be written quickly,

I need a script that will restore databases in parallel (at the same time) I am moving a bunch of prod dbs from one hosted environment to another and the existing connections set up for the transfer don't appear to support logshipping, nor facilitate quick transfers. So I am trying to speed up the restore process on the new side.

Not the actual restore portion, the how to run at the same time portion.


#2

If you are migrating to a new server AND your database is using FULL Recovery Model then there is a way to reduce the cut-over time dramatically (we've done it at client's busiest time of year with connected users not losing their session etc. with a holding page up for 15 or 20 seconds only).

The trick is to restore a recent full backup (with no time pressure) and then to restore a DIFF and then to restore TLog backups after that and then, in a time critical phase, to put Source database into ReadOnly (to stop any further access), make a final (small!!) Tlog backup, and restore that to the Target server, and then put the Target database live.

But perhaps that is not what you are trying to do?


#3

I've used exactly the same trick as Kristen but also went the route of doing a "Taillog" backup to take the original database essentially offline so that no more updates can happen. And it IS a real time saver, indeed!

I'll also warn you that parallel restores will actually slow your restores down (sometimes, by quite a bit) unless you can guarantee that each database lives on it's own private set of spindles. In this day and age of SANs and very large disks, it's not likely that you can guarantee that.


#4

I'd forgotten about that. That also means (I think?) that if the migration goes pear-shaped! that you can then just restore-and-resume (i.e. restore additional LOG backups from the new Target server back onto the original Source server), so its also a get-out-of-jail-free trick :slight_smile:


#5

Can't restore just the LOG backups from the new server. LSN chain will be all wrong because you would have had to take a full backup on the new server first to be able to take log backups.


#6

Could have sworn what I read didn't require that ... I'll see if I can find the LINK.


#7

Cool. If it's true, then I'll have learned something new. Thanks, Kristen.


#8

I'll be blowed if I can find it ... I could have sworn it was @ScottPletcher who told me ... but Googling specifically for posts at sqlteam.com found nothing relevant :frowning:

Blessed computer ought to be able to just find it for me based on my saying "Find that post which I seem tor recall" ... why the heck isn't the technology there yet?


#9

I wonder if it was this?

BACKUP LOG MyOriginalDB
TO disk = 'MyOriginalDB_yyyymmdd_hhmmss.trn'
WITH NORECOVERY;

??

If so then, sorry, but it was from "That Other Forum" !!

http://www.sqlservercentral.com/questions/Administration/131633/


#10

You can take log backups from the new server without first taking full or diff. The log chain is the same on both servers, so you can just start taking log backups there.


#11

I have the opportunity to check on that a week from Friday. Thanks for the tip, Tara.