I have MS SQL server with several jobs. Sometimes I and my colleagues change task. I want to export jobs, like at 
automaticaly so that put in mercurial and store version of jobs. I would also like to have a similar opportunity for stored procedures in the database
Add a comment |
2 Answers
Once you realize that SSMS is using SMO (Server Management Objects) to do the scripting, and that you can use SMO yourself directly through something like powershell, this sort of thing gets easy. For instance, here's scripting out all the jobs on your server (written in powershell):
pushd; import-module sqlps -disablenamechecking; popd; $s = new-object microsoft.sqlserver.management.smo.server 'yourServer'; $db = $s.Databases['yourDatabase']; foreach ($proc in $db.StoredProcedures | where {$_.IsSystemObject -eq $false}) { $proc.script(); } $a = $s.JobServer; foreach ($job in $a.Jobs) { $job.script(); } This is the least fancy version in that it dumps it straight to your screen. Want fancier? Create a ScriptingOptions object and pass that to the script() method. That should get you started.
- Thank you so much for the tip. But your example on line
$s = new-object sql.microsoft.sqlserver.managment.smo.server 'yourServer';I gave error:New-Object : Cannot find type [sql.microsoft.sqlserver.managment.smo.server]: make sure the assembly containing this type is loaded.I changed the code a bit and got a working example.Arty– Arty2015-04-24 09:25:46 +00:00Commented Apr 24, 2015 at 9:25 - 1
- It is the tragedy of a single character :-) I spent an hour on the alternative solution for this problem.Arty– Arty2015-04-25 20:38:54 +00:00Commented Apr 25, 2015 at 20:38
- I found alternative solution for export stored procedure -
sp_helptext 'stored_procedure_name'Arty– Arty2015-04-27 14:02:23 +00:00Commented Apr 27, 2015 at 14:02
In my case this example worked without error, thanks Ben Thul:
import-module sqlps -disablenamechecking; $jobs = Get-ChildItem sql\ServerName\default\jobserver\jobs foreach ($job in $jobs) { Write-host $job.Name #script(); $job.script()|out-file c:\out\jobs\$job.sql UTF8 } import-module sqlps -disablenamechecking; $StoredProcedures = Get-ChildItem sql\ServerName\default\Databases\DatabaseName\StoredProcedures foreach ($sp in $StoredProcedures) { $spname=$sp.Schema+"."+$sp.Name Write-host $sp.Name #script(); $sp.script()|out-file c:\out\sp\$spname.sql UTF8 } - 1Homework for you: get it to write each job/procedure to its own file. Hint: the ScriptingOptions object has a FileName property on it. Since you're ultimately putting this in version control, I think you'll find one file per object a bit more manageable. But excellent work!Ben Thul– Ben Thul2015-04-24 18:25:52 +00:00Commented Apr 24, 2015 at 18:25
- I did so - $job and $spname is variable. But I understood you about more simple use with script() and FileName.Arty– Arty2015-04-25 20:34:00 +00:00Commented Apr 25, 2015 at 20:34
- Ah... so you did! I missed the dollar sign in front of the output file name. It seems that we're trading meaningful one-character differences. :)Ben Thul– Ben Thul2015-04-26 17:11:31 +00:00Commented Apr 26, 2015 at 17:11