Migrating jobs from one server to other can be easily done with powershell. Let’s look into the script. At first, include reference to the relevant SMO assemblies and then create the source and destination sql instance objects.

The variable $sourceserver specifies the source server instance where as the variable $destinationserver specifies the destination server.The next step is to create scripting object to script the SQL Agent jobs.

With this being done, next step is to iterate through all jobs in the source server.

The above script iterates through all the jobs in source server; sets the owner of the job to sa if it’s not sa. This is done to avoid error in case the owner (login) at source server doesn’t exist at the destination server. To sync logins between instances you can use script from my other blog. It may be a possibility that some of the job coexist at both source and destination SQL instance. So, to migrate only non-existing job I filtered out the coexisting jobs in object $djobs. The next step is to create and script out the filtered jobs from the above script.

The above script checks if the object $djobs is empty and if it is, then it sets the script location and gets the job script into a stringcollection object $sc. The job script/query is then executed on destination server using the executenonquery method.

The complete script is available at http://sdrv.ms/14VdcKs



Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook