Click here to monitor SSC

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
How to Kill a Company in One Step or Save it in Three
 The majority of companies that suffer a major data loss subsequently go out of business. Wesley David... Read more...

Migrating from OCS 2007 R2 to Lync: Part 4
 Having migrated the rest of our users and legacy resources across and started getting ready to... Read more...

Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL... Read more...

Seth Godin: Big in the IT Business
 Seth Godin has transformed our understanding of marketing in IT. He invented the concept of 'permission... Read more...

Using SQL Test Database Unit Testing with TeamCity Continuous Integration
 With database applications, the process of test and integration can be frustratingly slow because so... Read more...