如何为SQL Server Agent作业添加版本号（不使用描述字段）

Great question—avoiding the description field makes total sense if you’re already using it for operational notes, or want to keep versioning metadata separate from core job documentation. The perfect solution here is to use SQL Server extended properties, which let you attach custom, persistent metadata directly to the job object without modifying any built-in job fields.

Step 1: Add/Update the Version Number as an Extended Property #

You can use system stored procedures to safely add a version property—this script checks if the property already exists, updates it if it does, or creates it if it doesn’t (so it won’t overwrite any existing metadata or job settings):

DECLARE @JobName NVARCHAR(128) = N'YourJobNameHere';
DECLARE @Version NVARCHAR(50) = N'1.2.3'; -- Your desired version number

-- Get the job ID
DECLARE @JobID UNIQUEIDENTIFIER;
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;

-- Check if the version property already exists
IF EXISTS (
    SELECT 1 
    FROM sys.extended_properties 
    WHERE major_id = @JobID 
      AND minor_id = 0 
      AND class = 109 -- Class ID for SQL Server Agent jobs
      AND name = N'JobVersion'
)
BEGIN
    -- Update existing version property
    EXEC msdb.dbo.sp_updateextendedproperty
        @name = N'JobVersion',
        @value = @Version,
        @level0type = N'SERVER',
        @level1type = N'JOB',
        @level1name = @JobName;
END
ELSE
BEGIN
    -- Create new version property
    EXEC msdb.dbo.sp_addextendedproperty
        @name = N'JobVersion',
        @value = @Version,
        @level0type = N'SERVER',
        @level1type = N'JOB',
        @level1name = @JobName;
END

Step 2: Retrieve the Version Number Later #

To check the version of a job, query the extended properties alongside job details:

SELECT 
    j.name AS JobName,
    ep.value AS JobVersion
FROM msdb.dbo.sysjobs j
LEFT JOIN sys.extended_properties ep 
    ON j.job_id = ep.major_id
    AND ep.class = 109
    AND ep.name = N'JobVersion'
WHERE j.name = N'YourJobNameHere';

Step 3: Integrate This Into Your Job Update Script #

Add the version property logic to the end of your existing job update script. This ensures that every time you deploy updates to the job, you can bump the version number without overwriting existing job configurations (like steps, schedules, or the description field).

For example, if your script uses sp_update_job to modify job settings, just append the version update code from Step 1, adjusting the @Version value to match your deployment version.

Why This Works #

• No interference with core fields: The description field remains untouched for its intended use.
• Persistent metadata: Extended properties are tied directly to the job object and survive job edits unless explicitly removed.
• Flexibility: You can add other custom properties (like LastDeployedDate or OwnerTeam) using the same pattern if needed.

内容的提问来源于stack exchange，提问作者James Jenkins