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

In a previous article, we highlighted that the msdb database stores practically all objects related to automation. In this article, we’ll review the case of moving jobs and objects between the SQL Server instances.

Let’s start with the list of objects stored in msdb on this instance of SQL Server.

We have several jobs created with a maintenance plan (see the article Creating Maintenance Plans in SQL Server). We also have two alerts and one operator. Msdb stores alerts and operators as well (see Figure 1). We shall delete these objects, and then recover them by restoring a backup of the msdb database.

Figure 1: Jobs, Alerts, and Operators

Viewing Objects Stored in msdb

If we query relevant system objects, we also see these objects returned as the result set. (See Listing 1, Figure 2). Msdb also stores the system catalogs with records of jobs, backup logs, operators, maintenance places, database mail, and other items related to automation.

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

To illustrate the concept for a single instance of SQL Server, we take a backup of the msdb database first. In production scenarios, regular backups of your system databases should be part of your strategy. They are usually small enough to fit into a daily full backup schedule comfortably.

Of course, when I refer to a system database, this does not include tempdb necessary. Moreover, a daily backup for a model database may not be required either — a weekly backup is sufficient. For the full daily backups, consider master and msdb.

Using the simple code in Listing 2, we take a backup of the msdb database.

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

Backup msdb

To illustrate the concept for a single instance of SQL Server, we take a backup of the msdb database first. In production scenarios, regular backups of your system databases should be part of your strategy. They are usually small enough to fit into a daily full backup schedule comfortably.

Of course, when I refer to a system database, this does not include tempdb necessary. Moreover, a daily backup for a model database may not be required either — a weekly backup is sufficient. For the full daily backups, consider master and msdb.

Using the simple code in Listing 2, we take a backup of the msdb database.

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

Dropping Jobs

Once the backup is ready, we drop the jobs on the instance. Note that dropping the jobs created by a maintenance plan requires to delete the maintenance plans that created them (See Figure 3).

Regular jobs can be removed by deleting them with the GUI. Another way is running the code from Listing 3, followed by the code from Listing 4.

Listing 3 generates the set of scripts necessary to drop the jobs. Then, in Listing 4, we execute scripts generated in Listing 3.

You can use this approach even though job names in your instance are probably different from mine.

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

After dropping the jobs, we can verify that there are no jobs left. Use the same script, as shown in Listing 1. We consider two ways for the scenario:

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

We initiate the restore operation using the script from Listing 5. In that script, we begin by setting the database to single_user mode. As someone or something (SQL Agent account?) might be logged into this database, it is necessary.

Then, we issue the “restore” command and set the new msdb database to multi_user. Note that we used the REPLACE option in the restore statement. If you are migrating msdb to a new instance, the REPLACE clause will be necessary. Without it, SQL Server may return an error about the backup set not belonging to the msdb database on the instance.

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

Once the restore operation is complete, the missing jobs and other objects are back. They come complete with their respective job histories. All relationships among the jobs with databases and other objects are intact. The jobs work as if no one and nothing ever deleted them.

Figure 6: All Jobs Restored

Conclusion

We can easily migrate jobs and similar objects from one SQL Server instance to another. For that, we need a backup and restore process of msdb. Similarly, we can recover these objects on an SQL Server instance if lost for some reason.

Originally published on https://codingsight.com/how-to-migrate-sql-server-jobs-from-one-sql-server-instance-to-another/, a community platform for database administrators and Microsoft stack technologies specialists.

Subscribe here to get more industry insides 👋