Loading . . .
dbatools – Migration of SQL Agent Backup Jobs

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 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 => https://www.sql-aus-hamburg.de/sql-server-backup-erstaunliche-grundlagen/

2 thoughts on “0

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.

Neue Features bringen Licht ins Dunkel Previous post TempDB-Performance-Tuning out of the box – T-SQL Tuesday #87
Azure SQL Database Next post First steps with Azure SQL Database and Powershell – Part 1

SQL from Hamburg

Das bin ich ;-)

Björn Peters - MVP - Data Platform
I had to do with MS SQL databases for the first time in 2000 and looked after these database systems for around seven years. From 2007 to 2019, I was employed as a database administrator and looked after many different SQL servers from medium-sized companies and large corporations from different industries.
Although I have some certificates, I get my insights and knowledge about the SQL Server purely from day-to-day business, reading / following numerous forums/blogs.
I'm not specialized in any topic, but I focus mostly on performance analysis.
Since the end of 2016, I have been the Azure Meetup Hamburg organizer and from April 2017 to June 2018, Cloud and Data Center Management MVP, and since July 2018, Data Platform MVP.