System Databases

AleksandarAleksandar Posts: 10
Had a quick search and couldnt find any info on this, but:

Is there any way to put system databases under version control? In my case i just wish to put MSDB under version control and filter out everything other than the table "sysjobsteps". This would effectively let me put jobs into version control.

Anyone have any ideas?

Comments

  • Just had the bright idea that i could just copy the data from that table to some other database. I personally dont need version control on the system databases anymore but i imagine for some reason, someone out there might.
  • Sounds like a great idea. Is an Agent Job represented in the system tables as a single record that can be moved to another SQL Server instance without modification?

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Unfortunately agent jobs are stored in terms of steps. One of the columns in the table is the Job ID, however you have to do a join with the sysjobs table to get its actual name. ive done this below:
    MERGE JOBS
     USING 
     (
    	Select
    		msdb.dbo.sysjobs.name AS [job_name], 
    		msdb.dbo.sysjobsteps.step_id AS [step_no], 
    		msdb.dbo.sysjobsteps.step_name AS [step_name],
    		msdb.dbo.sysjobsteps.command AS [step_details],
    		msdb.dbo.sysjobsteps.step_uid AS [step_uid]
    	From 
    		msdb.dbo.sysjobs
    		JOIN msdb.dbo.sysjobsteps
    		ON msdb.dbo.sysjobs.job_id=msdb.dbo.sysjobsteps.job_id
     ) AS TEMP
     
     ON     
     (		JOBS.STEP_UID = TEMP.step_uid
     )
     
    WHEN MATCHED AND -- If it exists AND has changes
    (
    			JOBS.JOB_NAME <> TEMP.job_name OR
    			JOBS.STEP_NO <> TEMP.step_no OR
    			JOBS.STEP_NAME <> TEMP.step_name OR
    			JOBS.STEP_DETAILS <> TEMP.step_details
    )
     THEN
    	UPDATE Set
    	JOBS.JOB_NAME = TEMP.job_name, 
    	JOBS.STEP_NO = TEMP.step_no,
    	JOBS.STEP_NAME = TEMP.step_name,
    	JOBS.STEP_DETAILS = TEMP.step_details,
    	JOBS.STEP_UID = TEMP.step_uid
    
    
    WHEN NOT MATCHED  AND TEMP.step_uid IS NOT NULL THEN  -- New files
    	INSERT (JOB_NAME, STEP_NO, STEP_NAME, STEP_DETAILS, STEP_UID)
    	VALUES (TEMP.job_name, TEMP.step_no,TEMP.step_name,TEMP.step_details,TEMP.step_uid)
    
    WHEN NOT MATCHED BY SOURCE THEN
    	DELETE
    
    ;
     
    

    Im new to sql so hopefully that makes sense and answers your question in some respect at least :)
Sign In or Register to comment.