StockSnap - Markus Spiske - Speeding

dbatools – Migration of SQL Agent Backup Jobs

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

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 done to disk or to URL or even 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 initiated by the central 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

doing backup with dbatools - THE powershell modules for all DBAs

used PowerShell modules from dbatools for backup

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!

Another blog post (German) can be found here =>

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.