May 8, 2014 at 4:14 pm
Hi all,
Previously I've ran scheduled jobs that backed up and shuffled the .bak files across, but is it possible for a scheduled task on Server 1, to run a restore script against Server 2?
This is for a one-time (well, 2-time) migration that needs to happen out of hours, and of course I'd like as much automated as possible.
May 8, 2014 at 11:35 pm
Jake Shelton (5/8/2014)
Hi all,Previously I've ran scheduled jobs that backed up and shuffled the .bak files across, but is it possible for a scheduled task on Server 1, to run a restore script against Server 2?
This is for a one-time (well, 2-time) migration that needs to happen out of hours, and of course I'd like as much automated as possible.
Yes.
One way would be to be execute the restore in a SSIS package or with SQLCMD against Server 2.
Another way would be for the backup job to start a restore SQL Agent job on Server 2. You could start the remote job using SSIS, or through linked server, or via the SQLCMD utility.
May 9, 2014 at 5:48 am
I'd probably go to PowerShell to run this. It's pretty easy to set the command to connect to the other server and run a restore operation. It's also very easy to do the file copy through PowerShell. The one hitch that you'd need to watch for, regardless of the method you use, is that your SQL Agent runs within a context that allows it to connect to that other server. Or, you'll have to use SQL logins.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply