What is the best way to include SQL Server Agent jobs in Source Control?

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?

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.


I wanted the solution file to contain everything that is needed for the SQL database process I had implemented, all in one place, to Create this solution AND Deploy this solution to any given server (in just a few clicks). Components in this solution are:

• Database schema

• SSIS package

• SQL Server configurations (master db “Credential” creation; msdb “Proxy” creation; SQL Agent “Job” creation).

I wanted to keep it all together for two reasons:

• Simplified deployment (SOURCE: Local Dev Instance / TARGET: Server Dev Instance)

• Solution documentation

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:

• Create the script files with the TSQL code. I used the CHECK FOR EXISTENCE method to avoid errors where I could possibly be creating something that already exists in the environment.

Reference: How to check if a Credential exists in a SQL Server instance?

Example:

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'MyJobNameInSqlAgent')
    BEGIN
        print 'Yep, found it!!!'

        --Really you need to add the script to create it...
    END

-
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:

• Pluralsight: "DB Project development in VS" by Robert Cain | Module: Data and Schema Comparisons, Pre and Post Deployment Scripts

First I reviewed this helpful Pluralsight Video. I was able to follow
along, and what I did was...


A) New Folder. Create a new folder in the project called
[ScriptsPostDeployment]


B) New Script. Create a new script to the folder called
[Create_SqlAgentJobs.sql].

• ERROR MSG: "You can only have one post-deployment script in visual studio database project"

You will get this error message if you try to create multiple script files with [Build Action="Post Deploy"].


In total my project needed not only this one script, but it need some other scripts as well-- Create_SqlAgentJobs.sql, Credential_GEORGES-bl0040ep.sql, ProxyAccount_GEORGES-bl0040ep.sql. The error was discussed and solved in this thread on StackOverFlow: SQL Server project executing multiple script post deploy.


In total the new folder has 4 scripts-- 1 to run the scripts; 3 scripts that need to be executed.

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...

```
/*
Deployment script for DBA

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "DBA"
:setvar DefaultFilePrefix "DBA"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END

GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
DECLARE @rc int, -- return code
@fn nvarchar(4000), -- file name for back up
@dir nvarchar(4000) -- backup directory

EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @dir output, 'no_output'
if (@rc = 0) SELECT @dir = @dir + N'\'

IF (@dir IS NULL)
BEGIN
EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @dir output, 'no_output'
if (@rc = 0) SELECT @dir = @dir + N'\'
END

IF (@dir IS NULL)
BEGIN
EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @dir output, 'no_output'
if (@rc = 0) SELECT @dir = @dir + N'\Backup\'
END

IF (@dir IS NULL)
BEGIN
SELECT @dir = N'$(Defa

StackExchange Database Administrators Q#202000, answer score: 16