Backup & restore procedures for msdb

Question

Backup & restore procedures for msdb

Mark T.

SSChasing Mays

Points: 648
More actions
August 8, 2007 at 12:17 pm

#125267
Hello,

We've hit a kink in our backup and restore procedures concerning the msdb database in the context of a recovery on another machine (server).

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

On the restored server (after a recovery), the maintenance plans and jobs do get restored but the problem is that they reference the old server (server's name, disks, etc...). Further, it is impossible to delete them using Server Management Studio. We get the following 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_SRVR1\INSTANCE1
Error Number: 229
Severity: 14
State: 5
Line Number: 1
...

Where SQL_SRVR1 is the name of the old server!

My questions are:
1. How do we delete these maintenance plans and jobs?
2. What is the proper way to backup and restore maintenance plans and jobs (in fact msdb) ? Is it by scripting thngs and recreating them on the recovery server in an empty msdb or is there a way to retore msdb properly from the crashed server?
Thanks for your help.

Regards,
Mark T.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Molly-239126 Old Hand Points: 318 More actions · Answer 1

I'm not sure if this is the proper way to do this but...I've run into this and after updating the originating_server column in the sysjobs table to the new server name the jobs ran fine.

Molly-239126 Old Hand Points: 318 More actions · Answer 2

Sorry, my idea was for SQL 2000. I've been switching between forums and forgot which forum I was replying to. I apologize.

Mark T. SSChasing Mays Points: 648 More actions · Answer 3

Thanks Molly. I guess I’ll keep looking.

Mark T.

Mark T. SSChasing Mays Points: 648 More actions · Answer 4

Well here’s a new one for us: but first a clarification is required regarding our actions on the restored database: On the first attempt to delete the jobs and maintenance plans from the recovered server 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 magically reappear beneath their respective nodes! Upon the second deletion we get the error mentioned in my first post.

This morning I took a look at the production server’s Maintenance Plans and Jobs (the source of the msdb backup) and surprise surprise: THEY WERE ALL GONE!!! Ok well not so drastic since I was able to restore from a previous msdb backup. But still this is a MAJOR BUG!!! Deleting a job or a plan on one 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 and I am naïve?

Appreciate your help.

Regards,

Mark Tabash

Brad McGehee SSCertifiable Points: 5282 More actions · Answer 5

The following is not a great solution, but one that works. Instead of relying on backups and restores of MSDB for job fault tolerance, script the jobs instead. This allows you then to modify the script to fit the new name(s) of the servers when you rerun the scripts. Of course, you will need to track and manage these scripts so you don't loose track of them.

Brad M. McGehee

Microsoft SQL Server MVP

Director of Education, Red Gate Software

Brad M. McGehee
DBA

Mark T. SSChasing Mays Points: 648 More actions · Answer 6

Hi Brad,

Thanks for your answer.

I can see how the scripts can be generated for the jobs but not for the Maintenance Plans... any ideas?

My other question remains: Is this normal behaviour when restoring msdb or is it a bug?

Regards,

Mark T.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

Do you really care to fix these issues? I assume you are either setting up a failover solution or this is simply a test restore. If the final piece of your recovery process is to rename the failover server ans change it's IP address to that of the production server, your maintenance plans will be very likely to work.

Mark T. SSChasing Mays Points: 648 More actions · Answer 8

Greetings Michael,

Yes I do care to fix these issues.
Sorry but your assumptions are wrong (read objectives below).
We always use different names and IP's for our "new" servers (mainly to avoid confusion).

The restoration has multiple objectives:

To test and refine our recovery procedures. Since it is a named instance, it is possible that some day we decide to restore it on a new server as the default instance (renaming the server and changing the IP is not possible in this scenario).
To setup a test server, where we can test things like service packs, different setups and configurations, etc...

In the end, the reason I want to fix these issues is because I have plans and jobs on the test server that I am not able to delete.

So my questions remain:

How do I remove the plans & jobs?
What is the proper way to backup plans & jobs (msdb)?

Regards,

Mark Tabash

Anders Pedersen SSChampion Points: 11410 More actions · Answer 9

I think Molly is on the right track, it might just be more complicated in 2005.

(I do not have a server I can test this on, just mentioning it to get you something to investigate)

sysjobs and sysjobservers are user tables in MSDB so can be updated. In those you now have a serverid or originating serverID. This ID can be found in sys.sysservers. I believe 0 will allways be the local server. You might be able to update the jobs to that ID.

Obviously, test this closely for any other side effects....

Mark T. SSChasing Mays Points: 648 More actions · Answer 10

Hello Anders,

I looked into Molly's suggestion and I stopped investigating when I saw that the originating_server_id was already 0 (local server) on the test server. I was not aware of the sys.sysservers view (thanks for the hint). Unfortunately there is no way to execute an update on it:

update sys.sysservers set srvname='SQL_SRVR2\INSTANCE1'

Produces:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

No luck L. I ran into the sp_dropserver and sp_addserver stored procedures that according to BOL:

sp_dropserver: Removes a server from the list of known remote and linked servers on the local instance of SQL Server.

sp_addserver: To access data from an OLE DB data source, you must provide SQL Server 2005 with the following information:

· The name of the OLE DB provider that exposes the data source.

· Any information the OLE DB provider must have to locate the source of the data.

· Either the name of an object that the OLE DB data source can expose as a rowset, or a query that can be sent to the OLE DB provider that will cause the data source to expose a rowset. The objects that can be exposed as rowsets are known as remote tables. The queries that generate rowsets are known as pass-through queries.

· Optionally, you can supply SQL Server with valid login IDs for the OLE DB data source.

At this point I want to learn more about these stored procs before I consider using them. I am mostly concerned about the adverse effects that they can have on the reporting services databases that are in place on the test machine/instance.

Regards,

Mark Tabash

Anders Pedersen SSChampion Points: 11410 More actions · Answer 11

Well, what is a test server for anyhow? Take backups, and do it on a Friday afternoon.