dbatools – Migration of SQL Agent Backup Jobs

You can think of what you want, but sometimes you have to jump over your own shadow… usually I’m an advocate of SQL Server’s own backup, whether it’s backup to disk, backup to URL or backup to network share, but here I had to give in and set up the backup using 3rd party tools. Said and done…

At this customer, we had already switched everything to 3rd party backup, but not backups initiated from the backup server, but SQL Server initiated. The SQL Server Agent starts the backup as a command line call from within an Agent Job. For this, I had to copy the backup jobs from an existing server, as well as all related SQL Server objects. I started using “Create Objects to NewQuery” but received the error message that the mail component or receiver is not existing or not configured on the destination side. So I had to first migrate the complete SQL-Mail configuration, take over the operators and if we are already on the mailing configuration, then I can also migrate the SQL Agent Alerts recommended by Brent Ozar

dbatools - THE powershell modules for all DBAs

used powershell modules from dbatools

Just think about how to migrate the configuration of SQL mail, operators and custom alerts from one SQL Server to the other without much effort, so I mean with SQL Server builtin methods… This is not really much effort, but still a little more than 5 minutes. So what is closer than to deal more intensively with the dbatools …
With following commands “Copy-SqlDatabaseMail” you can migrate the SQLMail configuration, with “Copy-SqlOperator” you’ll take over all operators, continuing with “Copy-SqlAlert” and finally copy the jobs. It is not that much work 😉

Copy-SqlDatabaseMail -Source SQLServer01 -Destination SQLServer02
Copy-SqlOperator -Source SQLServer01 -Destination SQLServer02
Copy-SqlAlert -Source SQLServer01 -Destination SQLServer02
Copy-SqlJob -Source SQLServer01 -Destination SQLServer02 -Jobs  Full-Backup, TLog-Backup

So only 4 lines of Powershell code to ease the work, also thanks to the unification of the parameters within dbatools which also eases the parameterization of the individual commands in a very simple way – to find a quick and uncomplicated way to do any migration. I have to recommend the use of dbatools to every DBA!

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.