Rodney

MSDB and the History of the World

Published Wednesday, September 05, 2007 6:49 AM

A few weeks ago I was tasked with moving SQL Agent jobs from one SQL box to another.  In my life as a DBA, these types of requests come up several times a year.  On a server with 2 or 3 jobs, even 5 to 10, it is easy enough to right-click your way through SSMS to create scripts for each job that can be run on the new server. Remember, two wrongs don't make a right, but three rights make a left. As far as I know there is no right-click Nirvana to "Copy All Jobs" or "Script All Jobs". 

So what if you have 30 to 100+ jobs? Well, for one you probably do not get much sleep at night unless you hide your Blackberry in a casserole dish in the cupboard. Secondly, the task of creating scripts in this one-off fashion would take the better part of a morning.

There are several other ways, of course, like scripting the task yourself. 

Or restoring the entire MSDB database to the new server, assuming that you can lose the one that is there now. This works well in a pinch, but this is an all or nothing endeavor and all of the jobs that are restored have the same status as the source. What if you only want 1/3 of the 100+ jobs to actually be enabled. This creates additional work.

Enter...Transfer Jobs Task in SSIS.  With this little tool from the BIDS toolbox you have the choice of moving all jobs or choosing from a list of jobs one or more to move. In addition, you can select if the jobs are to be enabled on the destination and also choose whether to overwrite or skip the job if it exists or fail the task entirely.

MSDB is an often overlooked database in terms of  importance.  I used to not give it a second glance. That was back when I had less than 10 servers to contend with. With 100+, it is critical and I make sure it is backed up and fed every night and take it outside for a walk every now and again just to let it know I care.

 

by Rodney

Comments

 

Phil Factor said:

Yes, there is a right-click Nivarna to script all jobs in Enterprise Manager. right click on jobs, ->all tasks->Generate SQL Script. I'm not sure what's happened to it in SSMS. (we programmers use DMO/SMO to do this sort of stuff) I agree that the process that Microsoft provides is as friendly as a cornered rat. Red-Gate should write a nice little job-deployment tool!
September 6, 2007 11:23 AM
 

Rodney said:

Yes, in this case, in my zeal to bypass all roads leading to Nirvana, my dogma was apparently run over by Phil's speeding Karma. Good catch on Enterprise Manager. I guess I just don't use it enough anymore.
Rodney
September 6, 2007 1:02 PM
 

scottyemcclain said:

Aye Lad, does EM still yet exist?  Or might, per chance, the whole philosophical reality (or is that an oxymoron?) be that...

"Since it is still supported... it exists".

ROFL
September 7, 2007 10:54 AM
You need to sign in to comment on this blog

















<September 2007>
SuMoTuWeThFrSa
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456
Larry Gonick: Geek of the Week
 Cartoonist, mathematician, historian and environmentalist. Larry Gonick proved that learning could be... Read more...

A SysAdmin's Guide to Change Management
 In the first in a series of monthly articles, ‘Confessions of a Sys Admin’, Matt describes the issues... Read more...

Exchange: Recovery Storage Groups
 It can happen at any time: You get a request, as Admin, from your company, to provide the contents of... Read more...

Build Your Own Virtualized Test Lab
 Desmond Lee explains the fundamentals of building a fully functional test lab for Windows Servers and... Read more...

Rendering Hierarchical Data with the Treeview
 It sometimes happens that Web Server controls that visualize data don't quite fit with the way that... Read more...