none
msdb restore on new server

    Question

  • 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?

     

    My questions are:

    1. How do we delete the maintenance plans and jobs from SQL_TEST without deleting them from SQL_PROD?
    2. What is the proper/recommended method to backup and restore maintenance plans and jobs (in fact msdb)? Is it by scripting things and recreating them on the recovery server in an empty msdb or is there a way to restore msdb properly from the crashed server?

    Thanks for your help. 

     

    Regards,

    Mark Tabash

     

    Thursday, August 09, 2007 4:13 PM

Answers

  • 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

    Tuesday, August 21, 2007 4:10 PM

All replies

  • once you restore your prod msdb in test server you need to change the server names id in the originating_server_id column of sysjobs table and then give a try.........yes scripting the jobs is an feasable option but if you have many jobs restoring is the ideal choice.........

    Thursday, August 09, 2007 5:35 PM
    Moderator
  • 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.

    Thursday, August 09, 2007 6:10 PM
  • 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

    Thursday, August 09, 2007 8:20 PM
  • 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

    Thursday, August 16, 2007 12:39 AM
  • 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

    Tuesday, August 21, 2007 4:10 PM
  • 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

    Tuesday, August 21, 2007 7:31 PM
  • 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

     

    Wednesday, August 22, 2007 11:53 PM
  •  

    Hi all ,

     

     Similar case - i have two SQL  2005 servers both are enterprise edition

     

    Source  Server A : SQL  2005 server Ent edition ( Version : 3050) (x86) - PROD

     

     Destination Server B : SQL  2005 server Ent edition ( Version : 3152) (64 bit)  - Backup

     

     I need to copy all SSIS packages and jobs from A to B(many packages exist stored in msdb and as file system) .

    http://support.microsoft.com/kb/264474  through this link i came to know both versions has to be same for restoring msdb .

     

    Source server A : SQL server services running under domain account

     

    Destination Server B : SQL server services running under  local system account.

     

     Both servers will be made to run under same domain account.(My top most important thing is that nothing should happen to source server in any aspect since its highly critical server)

     

     

    Can anyone tell me best possible method for completing this SSIS copy activity  ?

    Thursday, August 07, 2008 11:36 PM
  • Hi Rajganesh,

     

    Here's how I do it. Note that I am only interessted in maintenance plans since I don't have any other SSIS packages. So you will probably have to modify the script quite a bit. Also in my case, both servers are patched to the same level (3159). So I can't vouch on how this method will hold up when source and dest servers have different patch levels. Another difference between both cases, is that in my case all services on both servers run under the same domain account.

     

    Procedure:

     

    First create a database, call it maint_plan_db if you like, on both servers and in it create the following table:

     

    USE maint_plan_db

    GO

    CREATE TABLE dts_plan(
     id uniqueidentifier NOT NULL,
     packagedata image NOT NULL
    )


    Next on your source server A run the following query after having replaced the token <JOB_OWNER> with the login that will own the job on server B:

     

    SET NOCOUNT ON
    declare @subPlanId as uniqueidentifier,
       @id as uniqueidentifier,
       @planXML as xml,
       @planDesc as varchar(1024),
       @name as varchar(50),
       @jobId as uniqueidentifier,
       @jobName as sysname,
       @categoryId as int,
       @stepName as sysname,
       @schedId as int,
       @enabled as int,
       @schedName as sysname,
       @nextRunDate as int,
       @nextRunTime as int,
       @freqType as int,
       @freqInterval as int,
       @freqSubdayType as int,
       @freqSubdayInt as int,
       @freqRelInt as int,
       @freqRecFac as int,
       @actStartDate as int,
       @actEndDate as int,
       @actStartTime as int,
       @actEndTime as int,
       @dateCreated as datetime,
       @subPlanName as sysname

     

    --TRUNCATE TABLE maint_plan_db..dts_plan

     

    DECLARE c_plan INSENSITIVE CURSOR FOR
       select cast(cast(packagedata as varbinary(max)) as xml), id,
              subplan_id, job_id, name, isnull(d.description, 'MYORG '+name)
       from sysdtspackages90 d inner join sysmaintplan_subplans s
            on plan_id = id

    OPEN c_plan
    FETCH c_plan INTO @planXML, @id, @subPlanId, @jobId, @name, @planDesc

    WHILE (@@fetch_status = 0)
    BEGIN
       set @name=replace(@name, '''', '''''')
       set @planDesc=replace(@planDesc, '''', '''''')
       /* substitute current subplan_id and plan_id with tokens to be
          replaced at runtime in restore script
       */
       select @planXML=replace(cast(@planXML as varchar(max)), @id, '[PLAN_ID]')
       select @planXML=replace(cast(@planXML as varchar(max)), rtrim(@@servername), '[SERVER_NAME]')
       select @planXML=replace(cast(@planXML as varchar(max)), @subPlanId, '[SUB_PLAN_ID]')

       insert into maint_plan_db..dts_plan(id, packagedata) values(@id, cast(@planXML as varbinary(max)))
       print 'use [msdb]'
       print 'go'
       print '-- BEGIN
    '+@name+' PLAN CREATION'
       print 'declare @name as varchar(50),
              @id as uniqueidentifier,
              @desc as varchar(500),
              @date as datetime,
              @folderid as uniqueidentifier,
              @verid as uniqueidentifier,
              @jobId as uniqueidentifier,
              @jobGUID as uniqueidentifier,
              @schedule_id as int,
              @planXML as xml,
              @packagedata as varbinary(max),
              @subPlanGUID as uniqueidentifier'

       print 'SET NOCOUNT ON'
       print 'select @planXML=cast(cast(packagedata as varbinary(max)) as varchar(max))
              from maint_plan_db..dts_plan
              where id ='''+cast(@id as varchar(50))+''''
       print 'select @id=newid()'
       print 'select @verid=newid()'
       print 'select @subPlanGUID=newid()'
       print 'select @name='''+@name+''''
       print 'select @desc='''+@planDesc+''''
       print 'select @date=getdate()'
       print 'select @folderid=folderid from msdb..sysdtspackagefolders90 where foldername=''Maintenance Plans'''
       print 'select @planXML=replace(cast(@planXML as varchar(max)), ''[SERVER_NAME]'', rtrim(@@servername))'
       print 'select @planXML=replace(cast(@planXML as varchar(max)), ''[PLAN_ID]'', @id)'
       print 'select @planXML=replace(cast(@planXML as varchar(max)), ''[SUB_PLAN_ID]'', @subPlanGUID)'
       PRINT '-- ---------------------'
       print 'select @packagedata=cast(@planXML as varbinary(max))'
       print 'exec msdb..sp_dts_putpackage '+
               
    '@name, @id, @desc, @date,'+
               
    '@folderid, @packagedata, @packageformat=0,'+
               
    '@packagetype=6, @vermajor=1, @verminor=0,'+
               
    '@verbuild=1, @vercomments='''','+
               
    '@verid=''00000000-0000-0000-0000-000000000000'''
       PRINT '-- ---------------------'

       select @jobName=replace(name, '''', ''''''), @categoryId=category_id from msdb.dbo.sysjobs where job_id=@jobId
       print 'EXEC  msdb.dbo.sp_add_job @job_name='''+@jobName+''', @category_id='+cast(@categoryId as varchar(32))+', @job_id = @jobId OUTPUT'
       print 'select @jobId'

       select @stepName=replace(step_name, '''', '''''') from msdb.dbo.sysjobsteps where job_id=@jobId
       print 'EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name='''+@stepName+''',
         @step_id=1, @subsystem=N''SSIS'',
         @command=N''/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Backup" /set "\Package\Subplan.Disable;false"'''

       print 'EXEC msdb.dbo.sp_update_job @job_id=@jobId,
         @enabled=1,
         @start_step_id=1,
         @category_name=N''Database Maintenance'',
         @owner_login_name=N''<JOB_OWNER>'''

       print 'EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name = N''(local)'''

       SELECT @schedId=s.schedule_id, @schedName=replace(name, '''', ''''''), @enabled=enabled,
              @freqType=freq_type, @freqInterval=freq_interval, @freqSubdayType=freq_subday_type,
              @freqSubdayInt=freq_subday_interval, @freqRelInt=freq_relative_interval,
              @freqRecFac=freq_recurrence_factor, @actStartDate=active_start_date,
              @actEndDate=active_end_date, @actStartTime=active_start_time,
              @actEndTime=active_end_time, @dateCreated=date_created,
              @nextRunDate=js.next_run_date, @nextRunTime=js.next_run_time
       FROM msdb.dbo.sysjobschedules AS js
             JOIN msdb.dbo.sysschedules AS s
             ON js.schedule_id = s.schedule_id
       WHERE js.job_id = @jobId

       print 'EXEC msdb.dbo.sp_add_schedule @schedule_name=N'''+@schedName+''',
         @enabled='+cast(@enabled as varchar(32))+',
         @freq_type='+cast(@freqType as varchar(32))+',
         @freq_interval='+cast(@freqInterval as varchar(32))+',
         @freq_subday_type='+cast(@freqSubdayType as varchar(32))+',
         @freq_subday_interval='+cast(@freqSubdayInt as varchar(32))+',
         @freq_relative_interval='+cast(@freqRelInt as varchar(32))+',
         @freq_recurrence_factor='+cast(@freqRecFac as varchar(32))+',
         @active_start_date='+cast(@actStartDate as varchar(32))+',
         @active_end_date='+cast(@actEndDate as varchar(32))+',
         @active_start_time='+cast(@actStartTime as varchar(32))+',
         @active_end_time='+cast(@actEndTime as varchar(32))+', @schedule_id = @schedule_id OUTPUT'
       print 'select @schedule_id'

       print 'EXEC msdb.dbo.sp_attach_schedule @job_id=@jobId,@schedule_id=@schedule_id'

       select @subplanName=replace(subplan_name, '''', '''''') from sysmaintplan_subplans where plan_id=@id and job_id=@jobId
       print 'EXECUTE msdb..sp_maintplan_update_subplan
         @subplan_id=@subPlanGUID, 
         @plan_id=@id,    
         @name=N'''+@subPlanName+''', @description=N'''',
         @job_id=@jobId,
         @schedule_id=@schedule_id,
         @allow_create=1,
         @msx_job_id=NULL'
       print 'GO'

       PRINT '-- END '+@name+' PLAN CREATION'
       FETCH c_plan INTO @planXML, @id, @subPlanId, @jobId, @name, @planDesc
    END
    close c_plan
    deallocate c_plan


     

    Save the output of the script to a file (maintPlanCreate.sql). I use sqlcmd with the option -o to save the output.

    Now backup up the maint_plan_db database on server A and restore it on server B.

     

    Before going any further, I highly recommend backing up the msdb database on your destination server B, just in case the restore of the maintenance plans messes things up.

     

    Now on your destination server B execute the mainPlanCreate.sql script. This will restore the maintenance plans. After that you should verify that all paths and connections in the restored maint plans are valid.

     

    That's all.

     

    WARNING: Whatever you do, DO NOT RESTORE THE msdb DATABASE ON YOUR DESTINATION SERVER B. I tried it and eventhough it worked, I found out the hard way that any modifications or deletes you make on the restored database (server b) actually get applied on your source server A. Not good if you know what I mean.

     

    I guess you have to adapt a couple of things to get it working for your case. So best of luck to you!

     

    Mark T.

    Friday, August 08, 2008 3:17 AM