msdb restore on new server

Hello all!

First of all our servers:

• SQL_PROD: production server with SQL Server 2005
• SQL_TEST: test server with SQL Server 2005

What we are trying to do:

• We are testing our backup and recovery procedures.
• To do so we are using the backups from SQL_PROD and restoring them on SQL_TEST.
• We are restoring: all user databases, master database, msdb database and model database.

The issue we are experiencing:

I think we've hit a kink in our recovery procedures concerning msdb database in the context of a recovery on another machine (SQL_TEST in this case).

We include msdb in our nightly backups so that, in the event of server loss, our maintenance plans and jobs get restored.

On SQL_TEST (after a recovery), the maintenance plans and jobs do get restored but the problem is that they reference the SQL_PROD server (server's name, disks, etc...).

Deleting the jobs and maintenance plans from SQL_TEST we got no errors and the jobs and plans disappeared from beneath the Maintenance Plans and Jobs nodes. Doing a refresh on the nodes, the ¡°deleted¡± plans and jobs reappear beneath their respective nodes! Upon the second deletion (and any consecutive delete) we get the error:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
...

The SELECT permission was denied on the object 'sysmaintplan_plans', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)

Clicking on "Show technical details" reveals this:

...

Server Name: SQL_PROD
Error Number: 229
Severity: 14
State: 5
Line Number: 1
...

Note that SQL_PROD is being referenced in the error message!

This morning I took a look at SQL_PROD¡¯s Maintenance Plans and Jobs (the source of the msdb backup) and surprise surprise: THEY WERE ALL GONE!!! This is a MAJOR BUG!!! Deleting a job or a plan on one server should not have the effect of deleting it from a different server (regardless of the origins of the msdb database). Is this a known bug Or is it considered ¡°normal¡± behavior

Hi Deepak,

When I look in the sysjobs table the originating_server_id is 0 for all jobs. Where does this number come from and with what value should it be substitued

How can we script the Maintenance Plans

Again, I don't find it normal for a restore of msdb to this. Especially the fact that it deletes it on the production server when you think its deleting it on the test server. Worse even, why do the plans and jobs reappear on the test server after a refresh Worst of all, attempting to delete them again produces the error mentioned in my first post!

Thanks,

Mark T.

One more thing. According to this (reference http://support.microsoft.com/kb/915845):

...

-- Find the jobs to update. These jobs must match all of the input
-- criteria, unless all of the inputs are null. In this case,
-- examine all jobs. The jobs must also be jobs created locally,
-- such as sysjobs.originating_server_id = 0. These jobs should not be a job that we run
-- because another server told us to. Furthermore, if the job
-- is local but it is meant to be run on a target server, we send an
-- update for the job.
declare @jobsToUpdate TABLE (job_id uniqueidentifier not null)

insert into @jobsToUpdate
select job_id
from sysjobs
where originating_server_id = 0 -- local jobs
and ((COALESCE(@job_name, sysjobs.name) = sysjobs.name) and
(COALESCE(@job_id, sysjobs.job_id) = sysjobs.job_id) and
(COALESCE(@owner_name, suser_sname(sysjobs.owner_sid)) = suser_sname(sysjobs.owner_sid)))
...

The originating_server_id's in sysjobs on SQL_TEST is already 0 (local jobs).

If anyone has any details on how to script the Maintenance Plans then please let me know.

Thanks,

Mark Tabash

The server ids are stored in sys.sysservers in master. Try executing:

select *
from master.sys.sysservers

I doubt you can script maintenance plans in SQL 2005 as they are SSIS packages. But you can script the execution of the packages and if the packages are stored in SQL Server then you have the packages through the database restore. In your case, I would guess that this is the results you got. The problem is that your packages have references the original server. I would suspect that this is the gist of all the problems.

-Sue

Hi,

Thanks for the suggestions. I already read them in BOL though.

I am now able to script maintenance plans and restore them on a different server wihout having references to the originating server. In a nutshell, the packages are extracted from msdb..sysdtspackages90. All references to the originating server name, connections, paths and id's are replaced with tokens that get substitued at restore time. The schedules are also extracted (from msdb..sysjobschedules) and get restored on the target server. Basically I have one script that extracts the info, manipulates it and then outputs a script that re-creates each maintenance plan.

Regards,

Mark

At my sense, Microsoft should do something to facilitate restoring of msdb on another server. At least it should be well documented in BOL. There is nothing in BOL about this.

It is not normal that when someone restore msdb on a new server, the action made (in msdb) on this new server are silently done on the originating server wihtout any warning. This could lead to very bad result.

Also is not normal to have to work as hard to script a restoring procedure of msdb.

Carl

I would appreciate that someone from Microsoft commit itself on this thread and explain the idea underneath this suspect behavior.

Is it a bug! If not why is it this way (so hard to recover msdb on another machine)

Best regards.

Carl