Steps to Move SQL Server Agent Jobs Between SQL Server Instances
When migrating SQL Server databases or instance from one server to another server or while upgrading from lower version of SQL Server to higher SQL Server version, there are many tasks to move various objects from source SQL Server instance to destination SQL Server instance which include Logins, Server Roles, Linked Servers, Credentials, Jobs, Operators, etc. All these objects can be created before-hand before the go-live so that the amount of work can be reduced during the downtime window. These objects does not change often, so they are better created while preparing the server for testing itself. Below are some of the important steps to move jobs, alerts and operators from one SQL Server instance to another SQL Server instance. Also there are additional steps to take care once these objects are moved to the target server.
Steps to Move SQL Server Jobs, Alerts and Operators
– Open the SQL Server Management Studio, and then expand the Management folder.
– Expand SQL Server Agent, and then either right-click Alerts, Jobs, or Operators.
– Click All Tasks, and then click Generate SQL Script.
– Run the generated file against the destination SQL server instance.
Additional Tasks to Perform before Running the Generated Script
– Jobs created by the generated script may have old login as owner which is not present on the new SQL Server instance or can be left blank is based on logins, so logins must be moved before running the Jobs creation script.
– If any of the jobs names contain old instance name, then they must be replaced by the new instance name.
– If any of the jobs code contains old instance name, then they should be replaced by the new instance name.
– If any of the code inside jobs is dependent on a linked server or backup devices, then Linked servers and backup devices should be moved before creating jobs.
– Maintenance plans jobs must not be scripted because maintenance plans will be recreated from scratch on the new SQL server instance.
– Replication jobs should not be created because it is better to recreate the replication manually which will create the jobs as there could be considerable changes in replication from one SQL server version to another SQL server version.
– Alerts should be verified and any references to old server names or objects not present on new server should be changed accordingly.
– Operators should be verified and made sure all required dependent objects are already present on the new server, if not then they need to be first created. Any references to old server name or objects not present on new server should be removed.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.