Sql-server – Remote execution of SQL Agent job

powershellsql serversql-server-agent

Working on setting an automated restore of dbs from Production to the test/dev environments using Net App SQL Snap Manager. The prod job handles the kick off of the restore, nothing being ran on the test/dev sql agent side. Not really familiar with the Net App process either, being handled by storage team. My question is, without using linked servers, is it possible to kick off a SQL agent job on the test/dev box remotely from the prod box? Would powershell give me the functionality I'm looking for? Or would linked servers be the better way to go?

Thanks

Best Answer

You should be able to achieve this using plain SQLCMD from the production server, but have it pointing to your other server

sqlcmd -S "servername" -Q "execute msd.dbo.sp_start_job @job_name='MyJobOnRemoteServer'"

Related Solutions

Sql-server – SQL Agent powershell context reference

If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL.

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps#sql-server-agent-tokens

So, if you have a SQL 2008 PowerShell step, you can start it with:

$sqlInstance = "$(ESCAPE_DQUOTE(SRVR))"

You may need to use MACH (machine name) and INST (just instance name) instead, because with the default instance SRVR == MACH, but with named instances SRVR == MACH\INST.

Sql-server – Get Job id OR Job name from within executing job

You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps.

At the end of the article there's one example with jobid:

SELECT * FROM msdb.dbo.sysjobs
WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;

Best Answer

Related Solutions

Sql-server – SQL Agent powershell context reference

Sql-server – Get Job id OR Job name from within executing job

Related Question