Scripting SQL Agent Jobs 2005 and 2000

So I started working on a web site that would list out all the SQL Server Agent jobs for easy access. After several revisions I came up with a set of DTS packages that would hit each SQL server to store the Job list (sysjobs), Job Step Information (sysjobsteps), and Job History Information (sysjobhistory) into a single central server. From there I just created an ASP web page that allows me to see all jobs from a single source. I keep the last 10 days of history in my central repository.

So far it has been pretty handy. One cool thing about the web site is that it contains the actual command of the job step. So if I am in a pinch and I need to recreate a job for some reason, I can quickly pull the job up and check it out. Good for database restores where I do not bring the msdb database over in the restore.

I was also thinking that it would be handy to have the actual .sql script stored off in a location so I could actually just run a script to recreate the jobs. Handy for those servers that have two dozen complex jobs. Not such a big deal for servers that have a single backup job, but to recreate two dozen jobs on a server would take some time.

SQL Server 2000: Just browse down the the SQL Agent in Enterprise Manager and right click on Jobs…Choose the All Tasks option and Generate SQL Script. This will let you generate a single SQL Script for all the jobs. I just store this SQL off on a separate server. I run this periodically, especially when jobs are added/deleted, etc.

SQL Server 2005: Thanks to the helpful tip here: from Adam. You can just click on the jobs folder under SQL Server Agent, then on the right hand side in the Object Explorer Details you can highlight all the jobs and then Right Click, Script Job as…Create To…File, or New Query Window.

So now I have the web site to see a quick view of all my jobs and I also have the .sql Script to actually build the jobs if necessary. All of this to ease the recovery steps.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s