Remote execution of SQL Agent job

Question

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

Answer 1

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'"

Answer 2

You can use Powershell and call command Invoke-Sqlcmd to execute the command remotely.

Invoke-Sqlcmd -ServerInstance "InstanceName" -Database "master" -Query "BACKUP DATABASE ..."

Answer 3

you can use the remote machine to run sp_executesql command within the dynamic SQL.

exec server.master.dbo.sp_executesql 'BACKUP DATABASE DBNAME1 to disk='\\Server\Share\backupfilename.bak''

Or you can do this

exec server.master.dbo.sp_executesql 'BACKUP DATABASE [DBNAME1] to disk='\\Server\Share\backupfilename.bak''