从MSDB备份中恢复特定的作业定义?

我们被要求在过去的特定时间恢复特定的SQL 2005作业。 我们有从那个时间段的MSDB数据库备份,但我不能覆盖当前的MSDB数据库,因为我不想失去对该服务器上其他作业的最近更改。

如果我将其作为副本进行恢复(称为MSDB_old或其他),如何提取该工作的信息? 或者我需要通过临时服务器上的MSDB数据库进行恢复?

没有必要恢复到临时服务器,你可以恢复到你说的东西(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中的条目,如果你想要回历史logging
  • 在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 

这个脚本在接受的答案中松散地基于这个脚本。 它已针对SQL 2014进行了更新,包括exception处理,primefaces事务处理和其他一些改进。

 -- 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 

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

这只适用于如果msdb恢复为msdb – 这意味着你的情况下,你必须在备份服务器上恢复备份为msdb。

我想你可以将它作为msdb的副本进行恢复,然后使用连接手动将所有内容从各种msdb_copy.dbo.sysjobs / sysjobsteps / sysjobschedules / sysjobservers表中拉出。

希望这可以帮助!

嗨,我想补充squillman的回答。 我在2008 R2testing了它。

首先,通过使用sysjobschedules和sysschedules修复FK违规错误。

其次,通过运行dbo.sp_add_jobserver。

最后,让它遍历msdb_old.dbo.sysjobs中的所有作业以完成复制。

 DECLARE @JobID UNIQUEIDENTIFIER declare @jobname nvarchar(128) DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT job_id FROM msdb_old.dbo.sysjobs OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @JobID WHILE @@FETCH_STATUS = 0 BEGIN SELECT @jobname = name FROM msdb_old.dbo.sysjobs WHERE job_id=@jobid print @jobname print 'insert in sysjobs' INSERT msdb.dbo.sysjobs SELECT * FROM msdb_old.dbo.sysjobs WHERE job_id=@JobID print 'insert in sysjobsteps' INSERT msdb.dbo.sysjobsteps SELECT * FROM msdb_old.dbo.sysjobsteps WHERE job_id=@JobID print 'insert in sysjobhistory' 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 print '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 s.[schedule_id] ,s.[schedule_uid] ,s.[originating_server_id] ,s.[name] ,s.[owner_sid] ,s.[enabled] ,s.[freq_type] ,s.[freq_interval] ,s.[freq_subday_type] ,s.[freq_subday_interval] ,s.[freq_relative_interval] ,s.[freq_recurrence_factor] ,s.[active_start_date] ,s.[active_end_date] ,s.[active_start_time] ,s.[active_end_time] ,s.[date_created] ,s.[date_modified] ,s.[version_number] FROM msdb_old.dbo.sysschedules s, msdb_old.dbo.sysjobschedules j WHERE j.job_id=@JobID and s.schedule_id = j.schedule_id SET IDENTITY_INSERT msdb.dbo.sysschedules OFF print 'insert in sysjobschedules' INSERT msdb.dbo.sysjobschedules SELECT * FROM msdb_old.dbo.sysjobschedules WHERE job_id=@JobID print'exec adds job in server' use msdb EXEC dbo.sp_add_jobserver @job_id = @jobid FETCH NEXT FROM MY_CURSOR INTO @JobID END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR 

只要恢复到任何一个新的名称的服务器,我已经做了很多次之前…