Monday, March 23, 2009

Generar script para jobs en SQL Server 2005 2008

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:

No comments: