System Databases

Comments

• Aleksandar Posts: 10

  January 13, 2012 12:33AM

  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.

  0 · Share on Twitter
• David Atkinson Redgate › Posts: 1,458 Rose Gold 2

  January 13, 2012 5:37AM

  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

  0 · Share on Twitter
• Aleksandar Posts: 10

  January 15, 2012 9:05PM

  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

  0 · Share on Twitter