I found that if I script a SQL Server Agent job as 'create to' that the script starts with use msdb
– so I assume that jobs are stored in the msdb database?
What is the best way to add SQL Server jobs to a Visual Studio database project? I added the msdb database, to the database solution, but that doesn't seem to have any reference to jobs.
I would like the jobs to be deployed/updated along with a database… it seems like this is not possible?
Best Answer
I'm really glad you asked this question. I had this very same need for SQL Server jobs in a Visual Studio database project and job deployments along with a database.
So I set to work on figuring this out. Here is what I created and how I sourced this setup. It works great for the intended purposes.
Overview:
Reference: How to check if a Credential exists in a SQL Server instance?
Example:
Create a script file that executes the script files. More details below.
Publish the database project, and BOOM you've just done something awesome.
SSDT Setup:
You will get this error message if you try to create multiple script files with [Build Action="Post Deploy"].
ssdt-dbproj_DB-ScriptsPostDeployment.png
SSDT Publish:
I ran the Publish file and chose the option to "Generate Script". I viewed the script and... POST DEPLOYMENT code was automatically inserted to add the SQL AGENT JOB!! Success! Below is the script and script execution messages after running the query on the SQL instance in SSMS...