Restoring MSDB causes Log Shipping Failures

My situation is as follows, I have created a new Staging Server; we want everything to be as close as possible to our Production OLAP server, including the Job System...how best to do this?

I tired playing around with creating a linked server and copying stuff from msdb from primary to staging, but eventually I ran into problems with ID's and just couldn't make it work, so I decided to simply copy the msdb database from my OLAP server and restore it on my Staging server. Sounds simple right? Well, there were a number of problems that I ran into, specifically with regards to Log Shipping; with some help from Google and Twitter, I was able to resolve the issues and wanted to post the fixes to help any of you that run into the same issues.

FYI, I'm running SQL Server 2008 R2

First Error: Log Shipping jobs show a status of "Suspended" and I can't get them to do anything.
Fix: Thanks to http://www.sqlservercentral.com/Forums/Topic8379-7-1.aspx and user TVR, I found the answer; I needed to run:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go

And Restart the service.

Next problem: Log Shipping Copy's work fine, but Restores fail, saying "Error: Could not find a log backup file that could be applied to secondary database". This time help came from Twitter, where user @AngryPets a.k.a Michael K. Campbell pointed me in the direction of the msdb..log_shipping_monitor_secondary table. It took quite a bit of playing with it and the associated tables to figure out what needed to be reset/changed, but I was finally able to get it to work by doing this:

/*******************************
** Created By: Eric Zierdt **
** ericzierdt@gmail.com **
** ericemployed.blogspot.com **
*******************************/
UPDATE msdb.dbo.log_shipping_monitor_secondary
SET secondary_server = '[Staging_Server_Name]'
 , last_restored_file = NULL -- Need to get rid of last restore data
WHERE secondary_server = '[Production_Server_Name]'

UPDATE msdb.dbo.log_shipping_secondary
SET  monitor_server = '[Staging_Server_Name]'
WHERE monitor_server = '[Production_Server_Name]'

Simple enough, but took me forever to figure out.

Hope this helps, if so, drop me a comment.

Enable/Disable jobs

This post will probably not have mass appeal, but it's something I wrote recently that I find of value. The situation I was working with is that on our OLAP server, we have a number of "Invoke" agent jobs that fire off ever 2 minutes which go out and look to see if we need to process run our ETL's and process our cube. When we are doing releases or work on ETL's we want to disable all the Invoke jobs so they don't start. I'm sure you can think of other situations where this might be necessary outside of cube processing.

So one way to do this (arguably the faster way) is to open your job manager and highlight the invoke jobs and right-click and choose disable. But what if you have a few invokes disabled for one reason or another? You'll have to either remember which ones were disabled, or make a list, and make sure you don't re-enable those when you are done. Or, what if you want to wait for your current Invoke to end before you do the disable (not necessary, but what if), or you want to do it at a specific time of day?

I came up with a script that I put into two agent jobs to facilitate this. These can be scheduled and work pretty well.

The first job, creates a storage table, stores the id's for the enabled jobs, then disables them. (It checks to see if the job exists first and if so, error's out)

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NULL
BEGIN
 CREATE TABLE FRZ_ADMINDB.dbo.ActiveInvokeJobs (
    job_id UNIQUEIDENTIFIER
  , [Name] VARCHAR(255)
 )
 INSERT INTO msdb.dbo.ActiveInvokeJobs
 ( job_id, [Name])
 SELECT  job_id
     ,[Name]
 FROM    msdb.dbo.sysjobs S
 WHERE   enabled = 1
   AND name LIKE '%invoke%'

 UPDATE msdb.dbo.sysjobs
 SET  enabled = 0
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )
END
ELSE
BEGIN
 RAISERROR ('Storage Table Already Exists.',16,1);
END

The next agent job checks to see if the storage table exists, and if so enables the disabled jobs, then deletes the storage table.

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NOT NULL
BEGIN
 UPDATE msdb.dbo.sysjobs
 SET  enabled = 1
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )

 DROP TABLE msdb.dbo.ActiveInvokeJobs

END
ELSE
BEGIN
 RAISERROR ('Storage Table Does Not Exist.',16,1);
END