Post reply

msdb and jobs

  • May 31, 2007 at 7:25 am

    #68569

    No backup of msdb. All I have are the database files which I attached to a database called msdb2. How do I get the jobs back?

    Thanks.

     

     

  • May 31, 2007 at 8:25 am

    #710015

    You may try the following:

    Stop your SQL Server service.

    Overwrite existing msdb by your msdb2.

    Rename your file names.

    Re-start your SQL Server service.

  • May 31, 2007 at 8:39 am

    #710026

    Yes, or back up msdb2 and restore as msdb.  This has the advantage of avoiding stopping SQL Server (I think you can restore msdb without starting SQL Server with special parameters).

    John

  • June 1, 2007 at 4:27 am

    #710272

    First, try restoring MSDB from MSDB2 as someone else has already recommended.  If that doesn't work, for some reason, there is another option... BUT <insert caveat here>...

    I had a bizarre error the other day when I was trying to add a job step to one of my jobs.  I hit OK and then the job errored out, deleting all but the first three steps of the job.  I was able to restore those steps with the following code, but I warn you now to back up MSDB and MSDB2 before you do it if you choose to take this route.

    FYI: This is updating system catalogs. NOT a recommended practice:

    USE MSDB2

    Insert into sysjobsteps

    (Select *

    from msdb.dbo.sysjobsteps

    where Job_ID = <myjobID> )

    The assumption here is that you already have the jobs listed in sysjobs.  If you don't, you should be able to use the same code, but change the table name to sysjobs, as listed above.  You'll want to do this before you import the jobsteps.  Also, there's a sysjobschedules table that contains the schedules.

    I do not know if MSDB will allow you to insert into sysjobs or sysjobschedules and I cannot stress enough that this is NOT a recommended practice.  I only did it because there were recently other job changes that I did not want to overwrite by restoring MSDB from a backup file and it was only a few steps in one job that I was trying to fix.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • June 1, 2007 at 6:57 am

    #710314

    Thanks for all the help. The restore worked. Jerry.

Viewing 5 posts - 1 through 4 (of 4 total)

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