En escenarios de disaster recovery, muchas veces no contamos con un Cluster, el cual, posee el 100% de los objetos de una instancia, en su respectivo nodo pasivo.
De esta forma, por mas que implementemos soluciones de Mirroring o Log Shipping, tanto los Jobs, como la nomina de Usuarios, no es transferida al equipo salvavidas de nuestra organización.
Con el siguiente script, se genera en una carpeta que nosotros especificamos, la nomina de todos los jobs que posee el Agente de SQL, pudiendo filtrar por categoría del mismo.
Por otra parte, también se genera un script denominado ALL_Jobs.sql, el cual contiene la lista de todos los mismos, para poder correrlo de una vez y generar toda la lista de forma automática.
La ejecución del mismo es mediante este ejemplo:
EXEC sp_GenerateScriptJobs 'C:\Temp\ScriptJobs'
El Stored Procedure completo va a continuacion.
CREATE PROCEDURE [dbo].[sp_GenerateScriptJobs]
@ScriptFolder VARCHAR(256)
AS
IF EXISTS
(
SELECT name FROM sysobjects WHERE name = 'TmpSchedules'
)
BEGIN
DROP TABLE TmpSchedules
END
CREATE TABLE TmpSchedules (id int identity (1,1), schedule_id int)
IF EXISTS
(
SELECT name FROM sysobjects WHERE name = 'TmpJobs'
)
BEGIN
DROP TABLE TmpJobs
END
CREATE TABLE TmpJobs (id int identity (1,1), jobid uniqueidentifier, name varchar(500))
IF EXISTS
(
SELECT name FROM sysobjects WHERE name = 'TMP_jobs_content'
)
BEGIN
DROP TABLE TMP_jobs_content
END
CREATE TABLE TMP_jobs_content (id int identity (1,1), C text)
IF EXISTS
(
SELECT name FROM sysobjects WHERE name = 'TMP_jobs_content_GLOBAL'
)
BEGIN
DROP TABLE TMP_jobs_content_GLOBAL
END
CREATE TABLE TMP_jobs_content_GLOBAL(id int identity (1,1), C text)
TRUNCATE TABLE TmpJobs
TRUNCATE TABLE TmpSchedules
TRUNCATE TABLE TMP_jobs_content
SET NOCOUNT ON
DECLARE @strSource VARCHAR(8000)
DECLARE @strDescription VARCHAR(8000)
DECLARE @Record VARCHAR(8000)
DECLARE @Destination VARCHAR(500)
DECLARE @ObjectToken INT
DECLARE @objFileSystem INT
DECLARE @intResult INT
DECLARE @cmd VARCHAR(8000)
--- Creamos la Carpeta
SET @ScriptFolder=@ScriptFolder+'\'+convert(varchar(8),getdate(),112)+'\'+cast(datepart(hour,getdate())as varchar)+'hs\Jobs'
DECLARE @MakeDirectory VARCHAR(800)
SET @MakeDirectory ='MKDIR '+@ScriptFolder
EXEC xp_cmdshell @MakeDirectory
DECLARE @JobID uniqueidentifier,
@JobName varchar(128),
@JobCategory varchar (128),
@JobCategoryClass varchar(128),
@Now datetime,
@Nowtext varchar(30)
SELECT @Now = GETDATE()
SELECT @Nowtext = CAST(@Now as varchar(30))
INSERT INTO TmpJobs (jobid,name) SELECT job_id,REPLACE([name], '\', '') FROM msdb.dbo.SysJobs WITH (NOLOCK)
DECLARE @MaxJobs int,
@JobControl int
SELECT @JobControl = 1
SELECT @MaxJobs = MAX(id) FROM TmpJobs
--Create Jobs by looping through all the existing jobs on the server
WHILE (@JobControl <= @MaxJobs)
BEGIN --BEGIN Jobs
TRUNCATE TABLE TMP_jobs_content
TRUNCATE TABLE TmpSchedules
INSERT INTO TMP_jobs_content (C) select 'USE [msdb]'
INSERT INTO TMP_jobs_content (C) select 'GO'
INSERT INTO TMP_jobs_content (C) select ''
SELECT @JobID = JobID FROM TmpJobs WHERE id = @JobControl
SELECT @JobName = name FROM msdb.dbo.sysjobs_view WHERE Job_ID = @JobID
SELECT @JobCategory =sc.name, @JobCategoryClass = category_class FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories sc
ON sc.category_id = sj.category_id
WHERE Job_ID = @JobID
BEGIN TRY
INSERT INTO TMP_jobs_content (C) select '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
INSERT INTO TMP_jobs_content (C) select 'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + @JobName + ''')'
INSERT INTO TMP_jobs_content (C) select 'EXEC msdb.dbo.sp_delete_job @job_name= N''' + @JobName + ''''+ ', @delete_unused_schedule=1'
INSERT INTO TMP_jobs_content (C) select 'GO'
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
INSERT INTO TMP_jobs_content (C) select 'BEGIN TRANSACTION'
INSERT INTO TMP_jobs_content (C) select 'DECLARE @ReturnCode INT'
INSERT INTO TMP_jobs_content (C) select 'SELECT @ReturnCode = 0'
INSERT INTO TMP_jobs_content (C) select '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @Nowtext + ' ******/'
INSERT INTO TMP_jobs_content (C) select 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N''' + @JobCategory + ''' AND category_class = ' + @JobCategoryClass+ ')'
INSERT INTO TMP_jobs_content (C) select 'BEGIN'
INSERT INTO TMP_jobs_content (C) select 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name = N''' + @JobCategory + ''''
INSERT INTO TMP_jobs_content (C) select 'IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select 'END'
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select 'DECLARE @jobId BINARY(16)'
INSERT INTO TMP_jobs_content (C) select ''
DECLARE @enabled int,
@notify_level_eventlog int,
@notify_level_email int,
@notify_level_netsend int,
@notify_level_page int,
@delete_level int,
@description nvarchar(128),
@category_name nvarchar(128),
@owner_login_name nvarchar(128),
@notify_email_operator_name nvarchar(128)
SELECT @enabled = sj.enabled,
@notify_level_eventlog = sj.notify_level_eventlog,
@notify_level_email = sj.notify_level_email,
@notify_level_netsend = sj.notify_level_netsend,
@notify_level_page = sj.notify_level_page,
@delete_level = sj.delete_level,
@description = sj.[description],
@category_name = sc.name,
@owner_login_name = SUSER_NAME(sj.owner_sid),
@notify_email_operator_name = so.name
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories sc
ON sc.category_id = sj.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators so
ON sj.notify_email_operator_id = so.id
WHERE Job_ID = @JobID
INSERT INTO TMP_jobs_content (C) select 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N''' + REPLACE(@JobName, '\', '') + ''','
INSERT INTO TMP_jobs_content (C) select '@enabled=' + CAST(@enabled as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@notify_level_eventlog=' + CAST(@notify_level_eventlog as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@notify_level_email=' + CAST(@notify_level_email as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@notify_level_netsend=' + CAST(@notify_level_netsend as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@notify_level_page=' + CAST(@notify_level_page as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@delete_level=' + CAST(@delete_level as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@description=N''' + REPLACE(@description, '''','''''') + ''','
INSERT INTO TMP_jobs_content (C) select '@category_name=N''' + @category_name + ''','
INSERT INTO TMP_jobs_content (C) select '@owner_login_name=N''' + ISNULL(@owner_login_name,'sa') + ''','
IF @notify_email_operator_name IS NOT NULL
BEGIN
INSERT INTO TMP_jobs_content (C) select '@notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT'
END
ELSE
BEGIN
INSERT INTO TMP_jobs_content (C) select '@job_id = @JobID OUTPUT'
END
INSERT INTO TMP_jobs_content (C) select 'IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
INSERT INTO TMP_jobs_content (C) select ''
--CREATE STEPS
DECLARE @MaxSteps int,
@LoopControl int
SELECT @LoopControl = 1
SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
WHILE (@LoopControl <= @MaxSteps)
BEGIN
DECLARE @step_name nvarchar (128),
@step_id int,
@cmdexec_success_code int,
@on_success_action int,
@on_success_step_id int,
@on_fail_action int,
@on_fail_step_id int,
@retry_attempts int,
@retry_interval int,
@os_run_priority int,
@subsystem nvarchar (128),
@command nvarchar (max),
@database_name nvarchar(128),
@flags int
SELECT @step_name = step_name,
@step_id = step_id,
@cmdexec_success_code = cmdexec_success_code,
@on_success_action = on_success_action,
@on_success_step_id = on_success_step_id,
@on_fail_action = on_fail_action,
@on_fail_step_id = on_fail_step_id,
@retry_attempts = retry_attempts,
@retry_interval = retry_interval,
@os_run_priority = os_run_priority,
@subsystem = subsystem,
@command = command,
@database_name = database_name,
@flags = flags
FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
AND step_id = @LoopControl
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select '/****** Object: Step ' + @step_name + ' Script Date: ' + @Nowtext + '******/'
INSERT INTO TMP_jobs_content (C) select 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','
INSERT INTO TMP_jobs_content (C) select '@step_id=' + CAST(@step_id as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@cmdexec_success_code=' + CAST(@cmdexec_success_code as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@on_success_action=' + CAST(@on_success_action as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@on_success_step_id=' + CAST(@on_success_step_id as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@on_fail_action=' + CAST(@on_fail_action as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@on_fail_step_id=' + CAST(@on_fail_step_id as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@retry_attempts=' + CAST(@retry_attempts as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@retry_interval=' + CAST(@retry_interval as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@os_run_priority=' + CAST(@os_run_priority as varchar(30))+ ', @subsystem=N''' + @subsystem + ''','
INSERT INTO TMP_jobs_content (C) select '@command=N''' + REPLACE(@command, '''','''''') + ''','
INSERT INTO TMP_jobs_content (C) select '@database_name=N''' + @database_name + ''','
INSERT INTO TMP_jobs_content (C) select '@flags=' + CAST(@flags as varchar(30))
INSERT INTO TMP_jobs_content (C) select ''
SELECT @LoopControl = @LoopControl + 1
END -- End Steps While
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select 'IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
INSERT INTO TMP_jobs_content (C) select 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'
INSERT INTO TMP_jobs_content (C) select 'IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
INSERT INTO TMP_jobs_content (C) select ''
--CREATE SCHEDULES
DECLARE @MaxSchedules int,
@SchedulesLoopControl int
SELECT @SchedulesLoopControl = 1
TRUNCATE TABLE TmpSchedules
INSERT INTO TmpSchedules (schedule_id) SELECT schedule_id = sjs.schedule_id
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
--INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
WHERE sjs.Job_ID = @JobID
SELECT @MaxSchedules = MAX(id) FROM TmpSchedules
IF EXISTS (SELECT COUNT(*) FROM TmpSchedules)
BEGIN
WHILE (@SchedulesLoopControl <= @MaxSchedules)
BEGIN
DECLARE @name nvarchar(2000),
@sch_enabled int,
@freq_type int,
@freq_interval int,
@freq_subday_type int,
@freq_subday_interval int,
@freq_relative_interval int,
@freq_recurrence_factor int,
@active_start_date int,
@active_end_date int,
@active_start_time int,
@active_end_time int,
@schedule_uid nvarchar (50)
SELECT @name = name,
@sch_enabled = enabled,
@freq_type = freq_type,
@freq_interval = freq_interval,
@freq_subday_type = freq_subday_type,
@freq_subday_interval = freq_subday_interval,
@freq_relative_interval = freq_relative_interval,
@freq_recurrence_factor = freq_recurrence_factor,
@active_start_date = active_start_date,
@active_end_date = active_end_date,
@active_start_time = active_start_time,
@active_end_time = active_end_time,
@schedule_uid = schedule_uid
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
INNER JOIN TmpSchedules s ON ss.schedule_id = s.schedule_id
WHERE sjs.Job_ID = @JobID
AND s.id = @SchedulesLoopControl
INSERT INTO TMP_jobs_content (C) select 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + REPLACE(@name, '''','''''') + ''','
INSERT INTO TMP_jobs_content (C) select '@enabled=' + CAST(@sch_enabled as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@freq_type=' + CAST(@freq_type as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@freq_interval=' + CAST(@freq_interval as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@freq_subday_type=' + CAST(@freq_subday_type as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@freq_subday_interval=' + CAST(@freq_subday_interval as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@freq_relative_interval=' + CAST(@freq_relative_interval as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@freq_recurrence_factor=' + CAST(@freq_recurrence_factor as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@active_start_date=' + CAST(@active_start_date as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@active_end_date=' + CAST(@active_end_date as varchar(30))+ ','
INSERT INTO TMP_jobs_content (C) select '@active_start_time=' + CAST(@active_start_time as varchar (30)) + ','
INSERT INTO TMP_jobs_content (C) select '@active_end_time=' + CAST(@active_end_time as varchar (30)) --+ ','
--INSERT INTO TMP_jobs_content (C) select '@schedule_uid=N''' + @schedule_uid + ''''
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select 'IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
INSERT INTO TMP_jobs_content (C) select ''
SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1
END -- End Schedules While loop
END -- END IF (SELECT COUNT(*) FROM TmpSchedules) > 0
DECLARE @server_name varchar(30)
SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END
FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID
INSERT INTO TMP_jobs_content (C) select 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N''(' + @server_name + ')'''
INSERT INTO TMP_jobs_content (C) select 'IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
INSERT INTO TMP_jobs_content (C) select 'COMMIT TRANSACTION'
INSERT INTO TMP_jobs_content (C) select 'GOTO EndSave'
INSERT INTO TMP_jobs_content (C) select 'QuitWithRollback:'
INSERT INTO TMP_jobs_content (C) select ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
INSERT INTO TMP_jobs_content (C) select 'EndSave:'
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select 'GO'
INSERT INTO TMP_jobs_content (C) select ''
INSERT INTO TMP_jobs_content (C) select ''
--print '@JobControl:'+cast(@JobControl as varchar)
INSERT INTO TMP_jobs_content (C)
VALUES ('GO')
INSERT INTO TMP_jobs_content_GLOBAL (C)
select C from TMP_jobs_content
order by [id]
--- Inicio Generamos el archivo de Salida por cada Job
DECLARE cc CURSOR FAST_FORWARD FOR
SELECT C FROM master.dbo.TMP_jobs_content ORDER BY id
OPEN cc
FETCH NEXT FROM cc INTO @Record
SET @Destination = RTRIM(@ScriptFolder) + '\' +rtrim(REPLACE(@JobName, '\', '')) + '.SQL'
EXECUTE @intResult = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
EXECUTE @intResult = sp_OAMethod @objFileSystem , 'CreateTextFile' , @ObjectToken OUT, @Destination,2,True
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Record = @Record + ' ' + CHAR(13) + CHAR(10)
EXEC @intResult=sp_OAMethod @ObjectToken, 'Write', null,@Record
FETCH NEXT FROM cc INTO @Record
END
CLOSE cc
DEALLOCATE cc
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @Destination, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
END TRY
BEGIN CATCH
END CATCH;
SELECT @JobControl = @JobControl + 1
--- Fin Generamos el archivo de Salida por cada Job
END --End Jobs
--- Inicio Generamos el archivo de Salida por cada Job
DECLARE cc CURSOR FAST_FORWARD FOR
SELECT C FROM master.dbo.TMP_jobs_content_GLOBAL ORDER BY id
OPEN cc
FETCH NEXT FROM cc INTO @Record
SET @Destination = RTRIM(@ScriptFolder) + '\ALL_Jobs.SQL'
EXECUTE @intResult = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
EXECUTE @intResult = sp_OAMethod @objFileSystem , 'CreateTextFile' , @ObjectToken OUT, @Destination,2,True
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Record = @Record + ' ' + CHAR(13) + CHAR(10)
EXEC @intResult=sp_OAMethod @ObjectToken, 'Write', null,@Record
FETCH NEXT FROM cc INTO @Record
END
CLOSE cc
DEALLOCATE cc
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @Destination, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Podemos hacer lo mismo con una rutina de Power Shell.
A continuación adjunto el script, que es mucho mas corto.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null
#$RutaScripts = "\\servername\"
$RutaScripts = "c:\Temp\JobScript"
# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "SRV-GAUTENG\TCH"
$jobs = $s.JobServer.Jobs
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.FileName = $f
$scrp.Options.AppendToFile = $false
foreach ($job in $s.jobserver.jobs)
{
$a = $job.category
#if ($a.toupper() -eq "PRODUCCION")
# {
$filename = $job.name + ".sql"
write-host $filename
$f = [System.IO.Path]::Combine($RutaScripts, $filename)
out-file -filePath $f -inputobject "USE MASTER `nGO`n"
out-file -filePath $f -inputobject "DECLARE @jobId binary(16) `n SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'$job') `n
IF (@jobId IS NOT NULL) `n EXEC msdb.dbo.sp_delete_job @jobid `n go `n " -append
$scrp.Script($job) >> $f
out-file -filePath $f -inputobject "`n" -append
out-file -filePath $f -inputobject "`n EXEC msdb.dbo.sp_update_job @job_name=N'$job',@enabled=0 `n GO `n" -append
# }
# else
# {
# }
}
Tips de SQL Server y fuera de la oficina... 