Once upon a time, you had one job, well actually two jobs. The first SQL Server agent job was important, the other was a test. You have decided you do not need the test agent job. Quick right-click on a job name, remove, confirm, done. When you refreshed the list, the test job was still there, but the other one - the important job - was gone.
Panic started knocking on your door! Oh, if only you had jobs in the source control or scripted out as a backup.
You take a deep breath and a sip of cold water and look into your database backup folder: it is there. The msdb database full backup is there, and you are safe.
PS: This is based on a true story. No system databases were harmed.
Solution
While I was doing my research I found solutions to restore the job by (potentially) breaking the msdb database. I am not that keen to play with the system databases, at least not with those that pay for my meals.
I figured out there must be another way to restore the dropped jobs.
Prerequisites
Firstly, we have to restore a copy of the msdb database with the job in it. For instance, restore it as msdb_backup.
The next thing to do is to change the way SSMS treats the new line/carriage return: Query > Query Options... > Grid > Retain CR/LF on copy save. More on Pinal Dave's blog. This will preserve new lines, so for instance if the job command looked like that originally.
-- version SELECT @@VERSION -- servername SELECT @@SERVERNAME
Without the setting change, the restored step would have a single line and that would not mirror the original job.
-- version SELECT @@VERSION -- servername SELECT @@SERVERNAME
Process
This is the process that the script follows. Each one of these steps is implemented in the script.
- We need a category, so that is the first part of the script.
- We know the category, now we add the job: sp_add_job.
- The job needs the steps, so adding all the steps with sp_add_job_step.
- By default jobs start with step=1 however, sometimes it is not the case, so we need to update that: sp_update_job (setting this in point 2 will fail if step_id > 1).
- Jobs may have multiple schedules, so we add them all: sp_add_jobschedule.
- Finally, for the local jobs, we make sure to set the server: sp_add_jobserver.
Before we start though, there are few things that we need to set:
- USE [<msdb_backup>] run the script in the context of the restored MSDB backup
- @JobName is the name of the job that we are restoring
- @JobNameSuffix is the piece of string added at the end of the restored job name for instance, "_RESTORED"
Script
After all the variables we set, time to execute the script to generate a set of commands to recreate the job (see the example below the script):
-- use restored msdb database backup USE [msdb_backup]; -- set the bane of the job that needs to be restored DECLARE @JobName NVARCHAR(MAX) = N'NewJobToBeRestored_001_name'; -- this will add a suffix to the name of the job DECLARE @JobNameSuffix NVARCHAR(MAX) = N'_RESTORED'; DECLARE @Begin NVARCHAR(MAX) = CHAR(10) + 'BEGIN ' + CHAR(10); DECLARE @End NVARCHAR(MAX) = CHAR(10) + 'END ' + CHAR(10); DECLARE @SQLCommand NVARCHAR(MAX) = ''; DECLARE @Transaction NVARCHAR(MAX) = 'USE [msdb];' + CHAR(10) + 'BEGIN TRANSACTION DECLARE @ReturnCode INT = 0; '; DECLARE @ReturnCode NVARCHAR(MAX) = '@ReturnCode = ' DECLARE @Error NVARCHAR(MAX) = CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'; DECLARE @QuitWithRollback NVARCHAR(MAX) = 'COMMIT TRANSACTION' + CHAR(10) + 'GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' + CHAR(10) + 'EndSave:'; SET @SQLCommand = @Transaction + @SQLCommand; CREATE TABLE #JOBCREATION (id int identity(1,1),cmd nvarchar(max)); INSERT INTO #JOBCREATION SELECT @SQLCommand; -- 1. Category INSERT INTO #JOBCREATION SELECT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name= '+ 'N''' + sc.name + '''' + ' AND category_class=' + CAST(sc.category_class AS NVARCHAR(MAX)) +')' + @Begin + 'EXEC @ReturnCode = msdb.dbo.sp_add_category ' + '@class='+ 'N'''+ CAST(CASE sc.category_class WHEN 1 THEN 'JOB' WHEN 2 THEN 'ALERT' WHEN 3 THEN 'OPERATOR' ELSE 'N/A' END AS NVARCHAR) + '''' + ', ' + '@type='+ 'N'''+ CAST( CASE sc.category_type WHEN 1 THEN 'LOCAL' WHEN 2 THEN 'MULTISERVER' WHEN 3 THEN 'OPERATOR' ELSE 'N/A' END AS NVARCHAR(MAX)) + '''' + ', ' + '@name='+ 'N''' + sc.name + '''' + ';' + @Error + @End FROM dbo.sysjobs AS sj JOIN dbo.syscategories AS sc ON sj.category_id = sc.category_id WHERE sj.name = @JobName; -- 2. Add job INSERT INTO #JOBCREATION SELECT 'EXEC @ReturnCode = msdb.dbo.sp_add_job ' + '@job_name='+ 'N''' + sj.name + @JobNameSuffix + '''' + ', ' + '@enabled='+ CAST(sj.enabled AS NVARCHAR(MAX)) + ', ' + '@notify_email_operator_name='+ CAST(COALESCE(so.name,'''''') AS NVARCHAR(MAX)) + ', ' + '@notify_level_eventlog='+ CAST(sj.notify_level_eventlog AS NVARCHAR(MAX)) + ', ' + '@notify_level_email='+ CAST(sj.notify_level_email AS NVARCHAR(MAX)) + ', ' + '@notify_level_netsend='+ CAST(sj.notify_level_netsend AS NVARCHAR(MAX)) + ', ' + '@notify_level_page='+ CAST(sj.notify_level_page AS NVARCHAR(MAX)) + ', ' + '@delete_level='+ CAST(sj.delete_level AS NVARCHAR(MAX)) + ', ' + '@description='+ 'N''' + REPLACE(COALESCE(description, ''),'''','''''') + '''' + ', ' + '@category_name='+ 'N''' + CAST(sc.name AS NVARCHAR(MAX)) + '''' + ', ' + '@owner_login_name='+ 'N''' + COALESCE(CAST(SUSER_SNAME(owner_sid) AS NVARCHAR(MAX)),'') + '''' + ';' + @Error FROM dbo.sysjobs AS sj JOIN dbo.syscategories AS sc ON sj.category_id = sc.category_id LEFT JOIN dbo.sysoperators AS so ON sj.notify_email_operator_id = so.id WHERE sj.name = @JobName; -- 3. Add job steps INSERT INTO #JOBCREATION SELECT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep ' + '@job_name='+ 'N''' + CAST(sj.name + @JobNameSuffix AS NVARCHAR(MAX)) + '''' + ', ' + '@step_name='+ 'N''' + step_name + '''' + ', ' + '@step_id='+ CAST(step_id AS NVARCHAR) + ', ' + '@cmdexec_success_code='+ CAST(cmdexec_success_code AS NVARCHAR(MAX)) + ', ' + '@on_success_action='+ CAST(on_success_action AS NVARCHAR(MAX)) + ', ' + '@on_success_step_id='+ CAST(on_success_step_id AS NVARCHAR(MAX)) + ', ' + '@on_fail_action='+ CAST(on_fail_action AS NVARCHAR(MAX)) + ', ' + '@on_fail_step_id='+ CAST(on_fail_step_id AS NVARCHAR(MAX)) + ', ' + '@retry_attempts='+ CAST(retry_attempts AS NVARCHAR(MAX)) + ', ' + '@retry_interval='+ CAST(retry_interval AS NVARCHAR(MAX)) + ', ' + '@os_run_priority='+ CAST(os_run_priority AS NVARCHAR(MAX)) + ', ' + '@subsystem='+ 'N''' + subsystem + '''' + ', ' + '@command='+ 'N''' + REPLACE(COALESCE(command,''),'''','''''') + '''' + ', ' + '@database_name='+ 'N''' + COALESCE(database_name,'') + '''' + ', ' + '@output_file_name='+ 'N''' + COALESCE(output_file_name,'') + '''' + ', ' + '@flags='+ CAST(flags AS NVARCHAR(MAX)) + ';' + @Error FROM dbo.sysjobs AS sj JOIN dbo.sysjobsteps AS sjs ON sj.job_id = sjs.job_id WHERE sj.name = @JobName ORDER BY sjs.step_id ASC; -- 4. Startup step -- it is needed in case the job starts from step_id > 1 INSERT INTO #JOBCREATION SELECT 'EXEC @ReturnCode = msdb.dbo.sp_update_job ' + '@job_name='+ 'N''' + sj.name + @JobNameSuffix + '''' + ', ' + '@start_step_id='+ CAST([start_step_id] AS NVARCHAR(MAX)) + ';' + @Error FROM dbo.sysjobs AS sj WHERE sj.name = @JobName; -- 5. Add schedule INSERT INTO #JOBCREATION SELECT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule ' + '@job_name='+ 'N''' + CAST(sj.name + @JobNameSuffix AS NVARCHAR(MAX)) + '''' + ', ' + '@name='+ 'N''' + CAST(ss.name AS NVARCHAR(MAX)) + '''' + ', ' + '@enabled='+ CAST(ss.enabled AS NVARCHAR(MAX)) + ', ' + '@freq_type='+ CAST(ss.freq_type AS NVARCHAR(MAX)) + ', ' + '@freq_interval='+ CAST(ss.freq_interval AS NVARCHAR(MAX)) + ', ' + '@freq_subday_type='+ CAST(ss.freq_subday_type AS NVARCHAR(MAX)) + ', ' + '@freq_subday_interval='+ CAST(ss.freq_subday_interval AS NVARCHAR(MAX)) + ', ' + '@freq_relative_interval='+ CAST(ss.freq_relative_interval AS NVARCHAR(MAX)) + ', ' + '@freq_recurrence_factor='+ CAST(ss.freq_recurrence_factor AS NVARCHAR(MAX)) + ', ' + '@active_start_date='+ CAST(ss.active_start_date AS NVARCHAR(MAX)) + ', ' + '@active_end_date='+ CAST(ss.active_end_date AS NVARCHAR(MAX)) + ', ' + '@active_start_time='+ CAST(ss.active_start_time AS NVARCHAR(MAX)) + ', ' + '@active_end_time='+ CAST(ss.active_end_time AS NVARCHAR(MAX)) + ';' + @Error from sysjobschedules AS sjs join sysschedules AS ss on sjs.schedule_id = ss.schedule_id join sysjobs AS sj on sjs.job_id = sj.job_id WHERE sj.name = @JobName; -- 6. Add server INSERT INTO #JOBCREATION SELECT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver ' + '@job_name='+ 'N''' + CAST(sj.name + @JobNameSuffix AS NVARCHAR(MAX)) + '''' + ', ' + '@server_name = N''' + COALESCE(sts.server_name, '(LOCAL)') + '''' + ';' + @Error FROM sysjobservers AS sjs JOIN sysjobs AS sj ON sjs.job_id = sj.job_id LEFT JOIN systargetservers AS sts ON sjs.server_id = sts.server_id WHERE sj.name = @JobName; INSERT INTO #JOBCREATION SELECT @QuitWithRollback; -- see all the commands SELECT * FROM #JOBCREATION; -- drop the temp table DROP TABLE #JOBCREATION;
Output
After we run the script the output will look more or less like this. It is a result set made of commands to be executed.
![Restore SQL Agent job](https://www.sqlservercentral.com/wp-content/uploads/2021/01/2021-01-21-20_24_30-SQLQuery20.sql-localhost.msdb_backup-sa-54_-Microsoft-SQL-Server-Managem-1024x281.png)
What we need to do now is copy this output into a new query window and execute it.
Upon the successful execution, we can start breathing again as the job is there. Time for a tea.
![Restoring SQL Agent job](https://www.sqlservercentral.com/wp-content/uploads/2021/02/2021-02-02-16_50_29-SQLQuery8.sql-localhost14399.msdb-sqladmin-57_-Microsoft-SQL-Server-Man-1-1024x548.png)
Example
This is an example based on my environment and it generates all the stored procedures that we need in order to recreate the job. The goal of the script is to be as close as possible to the original (when we script out the job), therefore you can see the @ReturnCode variable and GOTO instructions.
USE [msdb]; BEGIN TRANSACTION DECLARE @ReturnCode INT = 0; IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name= N'Database Maintenance' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'NewJobToBeRestored_001_name_RESTORED', @enabled=1, @notify_email_operator_name=NewJobToBeRestored_001_operator, @notify_level_eventlog=2, @notify_level_email=3, @notify_level_netsend=0, @notify_level_page=0, @delete_level=1, @description=N'NewJobToBeRestored_001_description', @category_name=N'Database Maintenance', @owner_login_name=N'ToMove'; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=N'NewJobToBeRestored_001_name_RESTORED', @step_name=N'NewJobToBeRestored_001_Step01_name', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=5, @retry_interval=2, @os_run_priority=0, @subsystem=N'TSQL', @command=N'NewJobToBeRestored_001_Step01_command', @database_name=N'master', @output_file_name=N'C:tempNewJobToBeRestored_001_Step01_output.txt', @flags=22; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=N'NewJobToBeRestored_001_name_RESTORED', @step_name=N'NewJobToBeRestored_001_Step02_name', @step_id=2, @cmdexec_success_code=0, @on_success_action=4, @on_success_step_id=1, @on_fail_action=1, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'NewJobToBeRestored_001_Step02_command', @database_name=N'master', @output_file_name=N'', @flags=0; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_name=N'NewJobToBeRestored_001_name_RESTORED', @start_step_id=2; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'NewJobToBeRestored_001_name_RESTORED', @name=N'NewJobToBeRestored_001_schedule01', @enabled=1, @freq_type=8, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20210114, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'NewJobToBeRestored_001_name_RESTORED', @name=N'NewJobToBeRestored_001_schedule02', @enabled=1, @freq_type=64, @freq_interval=0, @freq_subday_type=0, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20210114, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'NewJobToBeRestored_001_name_RESTORED', @server_name = N'(LOCAL)'; IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
Final word
The script above may help you to restore a removed job from the msdb database backup without changing the msdb tables directly which may cause further issues.
Moreover, before restoring the job you can review each command from the output and update if necessary. Perhaps you do not need the alerts or schedules, similarly, you may want to add or remove steps.
Above all, nothing would be possible without the msdb backup, so please make sure you take it regularly and test that it is valid.
Thank you,
Mikey