In this series of articles, we will learn how we can clone the SQL Server instance using SSDT 2017. In my previous article, we learned how to Install and configure SSDT 2017 on Windows 10. In this series of articles, we will migrate the following SQL Server components to another server using SSIS tools provided in SSDT 2017:

1. SQL Server Jobs
2. Stored procedures of the master database
3. Transfer Logins
4. Transfer error messages

This article covers the process of transferring the SQL Jobs to another instance.

Environment Setup

For demonstration, I have installed SQL Server 2019 on my workstation. I have created two instances on SQL Server. I have restored the following databases.

1. Wideworld Importers database
2. Wideworld DW database
3. Stackoverflow 2010 database

The source instance is Nisarg-PC\SQL01, and the destination instance is Nisarg-PC\SQL02. The following jobs are created on Nisarg-PC\SQL01 (Source instance):

1. Diff Backup_User_Database
2. Full Backup_User_Database
3. Log Backup_User_Database
4. Rebuild Index_User_Database
5. Update Statistics_User_Database

You can view the list of jobs with schedules by executing the following query:

Configure Transfer Job Task

Let’s configure the Transfer Job Task to copy the SQL Jobs between the instances. To do that, launch SQL Server data tools 🡪 Create new SSIS project named Transfer maintenance jobs.

In package designer, drag the SQL transfer Jobs, drop it on the Control Flow window of package designer surface, rename it to Transfer Maintenance Jobs, and double-click to configure it.

A dialog box named Transfer Jobs Task Editor opens. Click on the Jobs option in the dialog box.

Now, to connect source and destination, we must create new SQL Server connections. To do that, click on SourceConnection. Select New connection.

In the SMO Connection Manager editor dialog box, specify the following parameters:

1. Server Name: Provide the Hostname of the Source Server. In our case, the source is Nisarg-PC\SQL01
2. Authentication Method: Provide an authentication method that is used to connect to the SQL Server. We will use windows authentication, so select the “Use Windows Authentication” option

Similarly, configure the Destination editor. The configuration parameters in the SMO connection Manager editor will be following:

1. Server Name: Provide the Hostname of the Source Server. In our case, the source is Nisarg-PC\SQL02
2. Authentication Method: Provide an authentication method that is used to connect to the SQL Server. We will use windows authentication, so select the “Use Windows Authentication” option

The Connection option looks like the following image:

Under the Jobs section, we can set the following options

1. TransferAllJobs: This option is used to specify whether you want to transfer all jobs or selected jobs. If you want to transfer all jobs, then select True. If you want to transfer selected jobs, then select False
2. JobList: This option is used when you want to transfer selected jobs. It shows a sub-menu that has all SQL Jobs created on the server. You can choose one or multiple jobs from the sub-menu

In our case, we are transferring all jobs, so select True in the TransferAllJobs option.

Under the options section, we can set the following options:

1. IfObjectExists: You can specify the action to be performed by the Transfer job task editor. You can set any of the following options
   1. FailTask: If a job exists on the destination instance, the job will fail
   2. Overwrite: If the job exists at the destination, the task will overwrite the settings of the existing job
   3. Skip: If the job exists, the task will skip the transfer of that specific SQL Job
2. EnableJobsAtDestination: Once all jobs are copied, if you want to enable the transferred jobs, Select True, and if you want to keep them disable, then select False

In our case, we want to overwrite the existing jobs, so select Overwrite, and after transferring jobs, we want to enable them, so the value of EnableJobsAtDestination is True.

The Transfer Job task looks like the following image:

Now, we should be notified when all jobs are transferred. To do that, we must configure the Notify Operator task. Let us understand how we can configure it.

Configure Notify Operator Task

First, drag the Notify operator task from the SSIS toolbox, drop it in the Control flow surface, rename it to Send an email, and double-click on it to configure.

First, we must configure the connection between the server on which the operator has been created. To do that, click on the New in Notify Operator task dialog box. Another dialog box, Connect Properties, opens. Specify the appropriate values of the following parameters.

1. Connection Name: Specify the desired connection name. In our demo, I have named it as SQLConnection
2. Server Name: Specify the servername on which the SQL Server operator has been created. I have already created an operator named DBASupport in Nisarg-PC\SQL01
3. Logon Information: Specify the authentication method. In our demo, I have used windows authentication

Click OK to close the dialog box.

Specify the email subject in the Notification message subject textbox. In our demo, the subject line is the Status of SQL Job migration.

Specify the email body in the notification message body. In our demo, the email body is following:

Hello DBASupport.,
The SQL Jobs have been transferred successfully.

Finally, the Notify operator task looks like the following image:

The email must be sent after all jobs are migrated successfully. To do that, we must connect both tasks using a connector. The package looks like the following:

The transfer job task has been configured successfully.

Summary

In the first article of the series, we learned about the Transfer jobs Task. I have explained how we can configure it to transfer the jobs between two instances of SQL Server using SQL Server Data Tools. (SSDT 2017) In the next article, we will learn about the Transfer Login task and create a package in SSDT 2017 to migrate logins between the two instances of SQL Server.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022