How To Migrate SQL Server Jobs From One SQL Server Instance To Another

Figure 1: Jobs, Alerts, and Operators

Viewing Objects Stored in msdb

-- Listing 1: Check List of Jobs in the Instance
use msdb
go
select @@SERVERNAME as ServerName
select name from sysjobs;
Figure 2: Viewing the List of Jobs Using T-SQL

Backup msdb

-- Listing 2: Backup msdb Database 
backup database msdb to disk = 'E:\DriveF\msdb_18072020.bak';

Backup msdb

-- Listing 2: Backup msdb Database 
backup database msdb to disk = 'E:\DriveF\msdb_18072020.bak';

Dropping Jobs

Figure 3: Delete Maintenance Plans
-- Listing 3: Generate Script to Drop Jobs
USE [msdb]
GO
select 'EXEC msdb.dbo.sp_delete_job @job_name=N''' + [name] + ''', @delete_unused_schedule=1' from sysjobs;
GO
Figure 4: Drop Jobs
-- Listing 4: Drop SQL Agent Jobs
EXEC msdb.dbo.sp_delete_job @job_name=N'DB1_BackupTransactionLog', @delete_unused_schedule=1
EXEC msdb.dbo.sp_delete_job @job_name=N'syspolicy_purge_history', @delete_unused_schedule=1
  1. Someone has accidentally deleted jobs and similar objects in an instance.
  2. We want to import jobs from one instance into another one.
Figure 5: All Jobs Dropped

Restoring msdb

-- Listing 5: Restore msdb database
use master
go
alter database msdb set single_user with rollback immediate;
GO
restore database msdb from disk = 'E:\DriveG\msdb_18072020.bak'
with replace;
GO
alter database msdb set multi_user;
GO
Figure 6: All Jobs Restored

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store