September 13, 2004 at 7:08 am
I am doing a full backup of a production database to another remote server. When this completes I want to update the database on the remote with the most current backup. I have created a package that restores the database on the remote server with the most current backup. I have it scheduled to do this after the backup but I want it to run on the successfull completion of the backup -not a schdule. Is it possible to have the successfull completion of the backup job on the production server trigger my restore/update job on the other server??? Or is there another way to do this?? Any ideas would be great thanks!
September 13, 2004 at 7:14 am
Why not use the 'execute package task'. You get to specify the server and the package name so should suit your purposes.
September 13, 2004 at 8:31 am
Jonathan that works well, I created a package that ran an SP that backed up the DB to the remote server and on success it executed the package task that ran the SP to restore the DB on the remote server. (Is there a better way to update the database on the remote server??) I was wondering, on the production server where I have a backup job that originaly backed up to the remote server could I add another step that would execute the package task in the same job or is that limited to DTS?? Thanks for your suggestion.
September 13, 2004 at 9:53 am
Apologies. I don't really get your meaning.
September 13, 2004 at 11:02 am
In EM when you create a backup device and then use and that device to backup a database it creates a job in SQL Agent. You can open those jobs and add multiple steps. My question was if you could include Execute Package Task as a step in one of those jobs. Are there T-SQL commands for Execute Package Task or is it only limited to DTS?? Thanks.
September 14, 2004 at 6:46 am
I tried once myself but could not get it to run.
See: http://www.sqldts.com/default.aspx?210
Sorry cannot help any further.
September 14, 2004 at 6:59 am
Hello,
I'm not sure I understand everything correctly from your posts, but I'll try to answer your original question from thread title - yes, it is possible to start a job on another server on successful completion of a job. Let's say your servers are named BACKUP_SERVER and RESTORE_SERVER. On the BACKUP_SERVER you have a job named 'AutoBackup' that creates a backup of your database, on the other server a job 'AutoRestore' that should start after backup is created successfully. Open the job AutoBackup, add another step with command
EXEC restore_server.msdb..sp_start_job 'AutoRestore'
Set the previous (backup) step to continue to this step only if backup was successful.
This command calls standard stored procedure with name (or ID - see BOL) of the job as a parameter, and server name makes it start on a different server than where the original (backup) job was running. If you don't specify server name, it will call a job on a server where it is running - if such exists.
Hope this helps, Vladan
September 14, 2004 at 8:11 am
I am doing the same thing. I have a server called Callserver and a warm start server called callserver2. I have a maintenance plan that created a full and transaction log backup jobs on server callserver. I created two 'restore' stored procedures in the master database on callserver2 (one for the full restore and one for the transaction log restore). The backups are saved to a shared network folder on server callserver. I made callserver2 a linked server on callserver and added the following step to the full database backup job:
declare @file_name varchar(100)
--find out of the name of the backup file created in the previous 'backup database' job step
select top 1 @file_name = '\\callserver' + substring(physical_device_name,
charindex('\', physical_device_name), 4000) from msdb.dbo.BackupMediaFamily bmf
left join msdb.dbo.BackupSet bs on bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'MortgagePro' and type = 'D'
ORDER BY bs.backup_start_date desc
exec callserver2.master.dbo.sp_restore_database_backups 'mortgagepro', @file_name
where 'mortgagepro' is the name of the database.
For the transaction log backup job I added an extra step:
declare @file_name varchar(100)
select top 1 @file_name = '\\callserver' + substring(physical_device_name,
charindex('\', physical_device_name), 4000) from msdb.dbo.BackupMediaFamily bmf
left join msdb.dbo.BackupSet bs on bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'MortgagePro' and type = 'L'
ORDER BY bs.backup_start_date desc
exec callserver2.master.dbo.sp_restore_log_backups 'mortgagepro', @file_name
When a stored procedure is called with the 4 part linked server name it actually runs the stored procedure on the linked server rather than the server the job is running on.
I hope this helps.
Peter
September 15, 2004 at 2:07 pm
Vladan thanks also for the help. I have an issue with my second step:
EXEC AVB1\AVB1.msdb..sp_start_job 'UpdateMTS_AVB1'
This errors cause of incorrect syntax near '\'
AVB1\AVB1 is another instance of SQL on my desktop any idea how to code for this???
September 16, 2004 at 3:03 am
Try exec [AVB1\AVB1].msdb..sp_start_job 'UpdateMTS_AVB1'
But, remember that you have to set up your 'restore' server up as a linked server and you can give it any alias you want on the backup server. It is the linked server alias you put in the command above.
Peter
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply