Yeh, was planning to post it, but was rushed with the earlier posting 😀
Just replace
"<< YOUR SERVER >>" with server name
"<< INSTANCE >>" with instance name, or DEFAULT if no instance.
"<< YOUR FOLDER >>" the folder you want the scripts saved in.
CD SQLSERVER:\SQL\<< YOUR SERVER >>\<< INSTANCE >>\JobServer\Jobs
$outDir = "<< YOUR FOLDER >>"
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("<< YOUR SERVER >>")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False
foreach ($Item in Get-ChildItem) {
$nm = $Item -replace ":", ""
$Scripter.Options.FileName=$outDir + "SQLAgentJob_" + $nm + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Options.ScriptDrops=$True;
$Scripter.Script($Item)
$Scripter.Options.AppendToFile=$True
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.ScriptDrops=$False;
$Scripter.Script($Item)
}
Couple of things I'll change when I get some spare time ( yeh right, who has spare time )
- Add server name/instance to file name
- Enhance the -replace part to do regex replace to remove invalid chars
- maybe see what static values can be replace with variables, or dynamic info
Enjoy
--------------------
Colt 45 - the original point and click interface