Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Wednesday, 24 June 2009

This script copies jobs from one msdb database to another, useful for when SQL Server is re-installed, and the DBA Forgot to script out the sql agent jobs; but you still have the old msdb mdf and ldf. 

Simply attach the database as msdb_old.

It is also possible to use this via a linked server and copy some sqlagent jobs from one server to another; this script is specific to sql server 2005. 

One item I had to adjust is that some of the jobs appeared "greyed" out afterwards and I had to open each job and adjust the target server to "local". 

But it was still quicker than re-creating all the jobs.  (I was unable to restore the previous version of the msdb database because it was at a lower service pack level than what the new server was built at - had I been smart as the new server was built there would have been a restore of the msdb database completed when the new server was at the same level, but I missed that step and was then left with the task of recreating all the jobs, argh).

I'd be cautious using this script if there were a lot of reporting subscription jobs (as there would have to be some synchronizations with the report database and website), but in this particular case all the jobs were custom backups, tlogs, exports, imports and other purely tsql and ssis related tasks.

http://www.lifeasbob.com/Code/ScriptVault.aspx?script_id=80 

 

Wednesday, 24 June 2009 09:43:24 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  SQL Agent#
Comments are closed.
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
Pied Piper of Grandview II
Buick drops the ball, er...muffler
Recent Posts
Archive
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll