Scripted or SSIS server 2 server restores?

  • I was wondering if there is a way to script out restores from serverA to serverB over a network? Ideally, I'd just have to change the filename and database name for each restore. I have over 200 restores that need to be done and was hoping that someone could suggest an easier way then using the GUI for this. Thanks in advance.

  • Hey there!

    Several ways to accomplish this. Really just depends how much T-SQL you want to do or if you prefer to work in SSIS.

    You could do this all via a SQL Agent job. Just backup the databases on server A as usually, then create Backup Devices on Server B pointing at each corresponding backup file for Server A, then scripted out the restores as a SQL Agent job.

    If you want to be uber cool you can use integration services to handle the whole process doing something like this.

    1.) Use the backup database function to backup all the dbs on ServerA

    2.) Maintain a table on one of your servers that lists all the variables (ie. Backup File Name (ServerA's backup file), Destination Server (where you are going to restore to), Destination Path (path on server b where the db file should be placed during restore), Database Name, etc) then use a for each loop against that directory containing the backups from server a, where the backup file name matches the file name in your table restore to the server indicated and to the path indicated in that same row. It's a little confusing to tell thru here but its actually pretty straight forward and can go a long way to simplify meeting a need like this. Just be sure which ever method you choose you make the process fault tolerant so if backup/restore 2 of 200 fails it doesn't prevent the rest of the steps from running (unless your business process prefers it that way).

  • That sounds somewhat difficult, is there a script out there that would give me a good basis?

    -Kyle

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply