Hoy me preguntaron de que forma se puede generar los scripts de creación de jobs ya creados en SQL Server, esto con el fin de poder ejecutarlos e instalarlos en otro servidor. La forma más sencilla es hacerlo con el Management Studio, o sea, elegir el job y dar botón derecho y elegir la opción Script Job As

 

ScriptSQLServerJobs_Simple

También se puede utilizar esta herramienta JobScripter.zip creada por Jens.

 

Tambien pueden utilizar este script que encontre en Internet

Listing 4: VB Code to Obtain a List of SQL Server Jobs
‘ Use oServer to connect to the appropriate SQL Server.
Dim oServer AS New SQLDMO.SQLServer    ‘ Server object
Dim oJob AS SQLDMO.Job                             ‘ Holds each job object
Dim myOutFile AS Integer                                ‘ Holds file handle

‘Connect to the server using trusted authentication.
oServer.LoginSecure = True
oServer.Connect ("(local)")                                ‘ Change if required
‘ Should check oServer.Status

‘Open up an output file and write header line.
myOutFile = FreeFile
Open "C:\ALLJOBS.SQL" FOR Output Shared AS #myOutFile ‘ Change if required
Print #myOutFile, "– Start of SQL to recreate all jobs" + vbCr

‘ Iterate through all the jobs; get name or script.

FOR Each oJob IN oServer.JobServer.Jobs
    ‘ Print #myOutFile, oJob.Name                          ‘ Get job name only and send to file.
    Print #myOutFile, oJob.Script                            ‘ Get CREATE script and send to file.
NEXT

‘Closing comment in file
Print #myOutFile, "– End of SQL to recreate all jobs" + vbCr

‘ Clean up
Set oServer = Nothing
Close #myOutFile

‘ End of code to paste

 

Slds

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags:

LiveJournal Tags: