SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Copy-SqlJob, a handy dbatools cmdlet

One of the things I’ve often needed to do is move jobs around between instances of SQL Server. I’ll often test a job on one instance, maybe run it for awhile to see if it’s a useful addition to my instance, and then deploy the same job to other instances.

I do this often with jobs that help each instance manage itself independently. While each new build often includes some standard settings and jobs, I do find that I’ll adjust jobs over time, fix bugs, enhance them, and I’ll need to move the job over. This has usually involved scripting the job on one instance, adding delete code, and then running the script on other instances.

No longer.

Enter Copy-SqlJob

One of the cmdlets in dbatools is Copy-SqlJob, which does what you’d expect. It copies jobs from instance to instance. If you look at the docs, but default it copies all jobs, but I rarely need that. Though I might start using that for new builds along with Copy-SqlDatabase to move my DBA database from machine to machine.

However, I can copy specific jobs, which is perfect for my purposes. Just put a list of jobs to be copied in a script and run that. Let’s see how this works. On one instance  I have a number of jobs.

2017-05-01 13_58_50-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

Another instance has fewer jobs.

2017-05-01 13_58_55-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

Let’s move just the CommandLog Cleanup and Output File Cleanup jobs over. I can fire up a PowerShell and go from there. I’ll start with the –WhatIf parameter, which lets me test without moving anything.

2017-05-01 14_06_19-powershell

That’s easy enough. Let’s just move one job for now, the CommandLog Cleanup job.

2017-05-01 14_07_08-powershell

And refreshing the second instance:

2017-05-01 14_07_18-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

That’s useful, and it works easily across these instances with Windows Auth. What if I need SQL Auth? I can use SourceSqlCredential and DestinationSqlCredential to specify accounts. I can even have this prompt me for the password:

2017-05-01 14_09_06-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

Once I type it in, the copy works.

What if I update a job on the source server, and need to ensure the changes get copied to the destination? I can use –Force. If I don’t, I’ll get a message that the job exists. With the Force parameter, the job gets copied.

2017-05-01 14_12_10-powershell

This is a simple, but handy way to move jobs between servers. Many of the dbatools cmdlets are designed for migrations, and this is no exception. You can migrate jobs easily if that’s your requirement.

I’d urge you to download dbatools and see which of these cmdlets might make your administration of SQL Server easier than you ever expected.

Filed under: Blog Tagged: dbatools, powershell, syndicated


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...