2

We recently had a server fail; on this server we had several jobs scheduled to run nightly. We've backed up the SQL Server (2005) database prior to the server dying and restored it, however I don't see any of the jobs in the SQL Server Agent. Does backing up the database not back up the jobs? I'm not aware of the jobs being saved anywhere... am I screwed?

5 Answers 5

9

The jobs are stored in the system database MSDB. Did you back that up? You need to restore it to get your jobs back.

4
  • I just asked the DBA here at work about this. His answer is to script out your jobs and keep those somewhere safe (source control, backed-up network drive, etc.). I get the impression that while simply restoring the MSDB will get your jobs back, it's a "sort of" solution that falls short of simply re-running a script to create and apply your scheduled jobs.
    – Mike C.
    May 29, 2009 at 13:08
  • 2
    No, restoring msdb this the ideal solution. Who will remember to re-do the script when a job changes? What about the job hstory that is also stored in msdb? What about indirect changes, such as operator email changes?
    – gbn
    May 29, 2009 at 13:16
  • "Who will remember to re-do the script when a job changes?" The DBA of course. After all the "A" in DBA is for "anal-retentive" right? :-) I don't know how it works in every shop, but every script and schema change to hit our production databases here is vetted by the DBAs and committed to source control. One could argue that they are a little over-board but we've not lost any data despite two server hardware failures in the last ten years. Who am I to argue with their obsessive compulsive brand of success?
    – Mike C.
    May 29, 2009 at 13:31
  • 1
    Restoring msdb is the ideal situation IF you plan on restoring to the same server or basically recovering a SQL Server intact on new hardware. If you have to merge it onto another one (such as getting a system back on-line to meet SLA or at DR when you collapse several servers onto fewer servers), then the backup option isn't the way to go, scripting is. May 29, 2009 at 13:46
3

You have two options. If you're planning on basically restoring a server intact, as 20th Century Boy indicated, the job information is stored in the msdb database. In addition to the user databases, you should be backing up master and msdb, which are system databases.

If you just want to extract the jobs to bring up on another, existing server, as a recovery option, you can script out the SQL Server Agent jobs.

  1. Open up SQL Server Management Studio
  2. Connect to the SQL Server in question using Object Explorer.
  3. Expand SQL Server Agent
  4. Expand the Jobs folder
  5. Right-click on the job to script and you should see Script Job as which have a couple of tiers of options.
0

This isn't strictly speaking a direct answer to the question, but I think it's relevant. Virtually any scheduled job that you'd normally create within SQL Server can be done as a batch file using osql or sqlcmd. You can run the batch file from the Windows Task Scheduler.

Which approach is better is a matter of taste. I like using batch files from Windows because I can run other stuff from the batch file, and the batch file is easy to replicate on new installations without worrying about restoring msdb.

JR

2
  • I wouldn't recommend this approach for the simple reason that its quirky. Who's going to look in windows task scheduler for sql jobs?! SQL Agent allows for much more advanced tasks, and its somewhere a DBA will look for tasks May 29, 2009 at 15:08
  • Or to go the other direction: it's possible to use SqlAgent as your job scheduler and kick off batch files/exe/scripts instead of Task Scheduler... not that's it's the idea course of action. :-)
    – Mike C.
    May 29, 2009 at 15:56
0

Scripting is good because you can load the scripts into a source code control system and keep a change log.

But you should be backing up MSDB also. Belts and suspenders.

0

For the situation you are in, as pointed out if you have neither an MSDB backup or a saved scripting of the jobs and MSDB is not available, you will have to recreate the jobs from memory or other sources. ("Am I screwed" = Yes)

When reinstating the jobs, make sure to not only look for what jobs you did have in place, but but jobs you should have had in place.

Now that it's a few weeks later and you've (hopefully) recovered most of your critical jobs, you should revisit what you have in place and think about:

  • Is your recovery model what it should be for the database's purpose
  • Are your backup jobs optimized for your recovery model and point in time recovery needs?
  • Are you maintaining indexes as you should be?
  • Are you maintaining statistics as you should be?

It can be difficult to recover in a situation like this, but it's much better if you make sure that you get the jobs back in a situation where you won't have performance issues down the road that bring up problems in the recovery again!

(Of course these are things one should check on all DB servers, but following up on a failure is a great time to re-examine best practices and see what you may be missing.)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .