Calling a SQL Agent Job from another Job on a remote server in SQL Server

Today I got request from client to automate database restore from prod server to destination server, One of its requirement was to call restore job on destination dev server  from prod server

Below is code to trigger SQL agent job from another remote server.

You can also download same from download

USE master

GO

/*Enable xp_cmdshell configurations*/

--To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

-- To enable XP_Cmdshell feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE WITH OVERRIDE

GO

/*Code to run job on remote server start*/

/*Declare variables*/

declare @retcode int

declare @job_name varchar(300)

declare @server_name varchar(200)

declare @query varchar(8000)

declare @cmd varchar(8000)

/*Initialize variables*/

set @job_name = 'Job to be triggered on destination server' --/*Update job name here.*/

set @server_name = 'Destination Server name' ---/*Update server name here, If require please also add port with server name.*/

set @query = 'exec msdb.dbo.sp_start_job @job_name = ''' + @job_name + ''''

set @cmd = 'osql -E -S ' + @server_name + ' -Q "' + @query + '"'

/*Print variable values to confirm values*/

print ' @job_name = ' +isnull(@job_name,'NULL @job_name')

print ' @server_name = ' +isnull(@server_name,'NULL @server_name')

print ' @query = ' +isnull(@query,'NULL @query')

print ' @cmd = ' +isnull(@cmd,'NULL @cmd')

/*Trigger the job*/

exec @retcode = xp_cmdshell @cmd

/*Code to run job on remote server end*/