Sql-server – Automaticaly export Jobs and stored procedure from mssql server

sql serversql-server-2005

I have MS SQL server with several jobs. Sometimes I and my colleagues change task. I want to export jobs, like at enter image description here 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

Best Answer

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.

Related Topic