Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate job script from existing jobs Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 12:40 PM
Points: 105, Visits: 109
We have around 40+ jobs in the server box which hosts 5 DB instances
, of which i need to script some of the jobs to be run on a different server for testing.

I need all the jobs which use a particuler DB only .

Is there any Way i can filter and genrate scripts from SQLAgent,
If i give a 'wordsearch' like the DB name ,

Please advice....
Post #1478171
Posted Monday, July 29, 2013 11:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 12:40 PM
Points: 105, Visits: 109
any suggestions??
Post #1478677
Posted Monday, July 29, 2013 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2014 12:32 PM
Points: 22, Visits: 143
You could query the MSDB to search for jobs with the DatabaseName you're interested in. At least that would give you the list of jobs you then would need to script manually through SSMS.


select distinct j.name from sysjobs j join sysjobsteps s on j.job_id = s.job_id
where s.command like '%DatabaseName%'


Post #1478693
Posted Monday, July 29, 2013 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 12,885, Visits: 31,826
there are dozens of scripts here on SSC for scripting out jobs:

http://www.sqlservercentral.com/search/?q=script+jobs&t=s

so from there you could then add a WHERE filter to any of those to limit to a specific database.

some jobs never explicitly mention a database name even thought hey fiddle with them...they might be querying sys.databases, or using dynamic sql to get database names, and run a loop / cursor thru the contents...so you need to take that into consideration as well.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1478707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse