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?
The jobs are stored in the system database MSDB. Did you back that up? You need to restore it to get your jobs back.
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.
- Open up SQL Server Management Studio
- Connect to the SQL Server in question using Object Explorer.
- Expand SQL Server Agent
- Expand the Jobs folder
- Right-click on the job to script and you should see Script Job as which have a couple of tiers of options.
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.
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.
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.)