1

I am trying to trigger a SQL Server Agent Job (takes a backup of the db and places into a directory) from python. Unfortunately, I haven't found anything in regards to python triggering a SQL Server Agent Job (only the other way around, SQL Server Agent Job triggering a python script).

Once I get that backup, I want to restore this db into a different SQL Server using the same python script.

Thanks for any help!!

Elliot Gitter
  • 60
  • 8

1 Answers1

2

You can run a job from Transact-SQL from Python:

EXEC dbo.sp_start_job N'My Job Name';  
GO

See documentation for more information.

jurez
  • 4,436
  • 2
  • 12
  • 20
  • I am having trouble with this because when I do that, I don't know what dbo to point it too. The SQL Server Agent Job is not in the 'Databases' folder but outside of it. So when I run this code, it says, 'Could not find stored procedure 'dbo.sp_start_job'.' Does that make sense? And I when I try to say 'Use servername' it says that the server name isn't a Db. I'm not sure I'm understanding but it feels like in my case, the SQL Server Agent doesn't live in a Db, but that's where everyone elses does (almost like a SP) – Elliot Gitter Jan 24 '22 at 21:59
  • First you need a valid connection string to connect to the database. That includes server, port, username and password (unless you have configured a different authentication). From there on, you need permissions to execute this SP (you can read more about that on that documentation page under "Permissions"). – jurez Jan 25 '22 at 08:32
  • 1
    @ElliotGitter Try `USE msdb` first, or `EXEC msdb.dbo.sp_start_job ...`. Because SQL Server Agent uses the `msdb` system database – Charlieface Jan 25 '22 at 23:18