☰

从 MSDB 备份恢复特定作业定义？

tag-icon • tag-icon sql-server sql-server-2005
从 MSDB 备份恢复特定作业定义？

我们被要求从过去某个时间的状态恢复特定的 SQL 2005 作业。我们有该时间段的 MSDB 数据库备份，但我无法覆盖当前的 MSDB 数据库，因为我不想丢失该服务器上其他作业的近期更改。

如果我将其恢复为副本（称为 MSDB_old 或类似名称），我该如何提取该作业的信息？还是我需要在临时服务器上通过 MSDB 数据库恢复它？

答案1

无需恢复到临时服务器，您可以将其恢复到您所说的内容（MSDB_old）并执行查询以恢复您的工作：

USE msdb_old
SELECT * FROM sysjobs
JOIN sysjobsteps ON sysjobs.job_id=sysjobsteps.job_id
WHERE sysjobs.NAME='My Lost Job'
ORDER BY sysjobsteps.step_id

你必须恢复

  • sysjobs 中的条目
  • sysjobsteps 中的每个条目都与 sysjobs 中的上述条目相关
  • 如果你想返回历史记录，请进入 sysjobhistory
  • sysjobschedules 中的条目来恢复您的计划

编辑：这是一个可以在 SQL 2005 和 2008 中执行此操作的脚本（假设您的工作名为“我的丢失的工作”并且您已恢复到 MSDB_Old）

DECLARE @JobID UNIQUEIDENTIFIER
SELECT @JobID = job_id FROM msdb_old.dbo.sysjobs WHERE NAME='My Lost Job'

INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_old.dbo.sysjobs
WHERE job_id=@JobID

INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_old.dbo.sysjobsteps
WHERE job_id=@JobID

SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
    (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
     [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
     operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
    instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
    [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
    operator_id_netsent,operator_id_paged,retries_attempted,[server]
FROM msdb_old.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

INSERT msdb.dbo.sysjobschedules
SELECT * FROM msdb_old.dbo.sysjobschedules
WHERE job_id=@JobID

答案2

剧本大致基于这个在接受的答案中。它已针对 SQL 2014 进行了更新，包括异常处理、原子事务和一些其他改进。

-- Script for SQL 2014
DECLARE @JobID UNIQUEIDENTIFIER
declare @servername sysname

set @servername = @@SERVERNAME

SELECT @JobID = job_id 
FROM msdb_old.dbo.sysjobs 
WHERE name='My Lost Job'

BEGIN TRAN

BEGIN TRY

INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_old.dbo.sysjobs
WHERE job_id=@JobID

INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_old.dbo.sysjobsteps
WHERE job_id=@JobID

SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
 (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
 [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
 operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
 instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
 [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
 operator_id_netsent,operator_id_paged,retries_attempted,[server]
FROM msdb_old.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

-- New insert in sysschedules 
SET IDENTITY_INSERT msdb.dbo.sysschedules ON
INSERT msdb.dbo.sysschedules (schedule_id, schedule_uid,
         originating_server_id, name, owner_sid, enabled,
         freq_type,freq_interval, freq_subday_type,
         freq_subday_interval, freq_relative_interval,
         freq_recurrence_factor, active_start_date, 
         active_end_date, active_start_time, active_end_time,
         date_created, date_modified, version_number)
SELECT schedule_id, schedule_uid, originating_server_id, name,
       owner_sid, enabled, freq_type, freq_interval, freq_subday_type,
       freq_subday_interval, freq_relative_interval,
       freq_recurrence_factor, active_start_date, active_end_date,
       active_start_time, active_end_time, date_created, date_modified,
       version_number 
FROM msdb_old.dbo.sysschedules a
WHERE schedule_id = (select schedule_id from msdb_old.dbo.sysjobschedules b where job_id=@JobID )
SET IDENTITY_INSERT msdb.dbo.sysschedules OFF

INSERT msdb.dbo.sysjobschedules
SELECT * FROM msdb_old.dbo.sysjobschedules
WHERE job_id=@JobID

-- Alter job as local job
EXEC msdb.dbo.sp_add_jobserver @job_id=@JobID, @server_name = @servername

END TRY
BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

    ROLLBACK TRAN

    RETURN
END CATCH

COMMIT TRAN

答案3

从 MSDB 中提取单个作业的最简单方法是右键单击 SSMS 中的作业并说脚本作业 - 然后将脚本带到目标服务器并运行它以重新创建作业（可能需要进行一些修改）。

这仅当 msdb 恢复为 msdb 时才有效 - 这意味着在您的情况下您必须在临时服务器上将备份恢复为 msdb。

我猜您可以将其恢复为 msdb 的副本，然后使用连接手动将所有内容从各个 msdb_copy.dbo.sysjobs/sysjobsteps/sysjobschedules/sysjobservers 表中提取出来。

希望这可以帮助！

答案4

只需用新名称恢复到任何服务器，我之前已经做过很多次了......

相关内容