How to run a backup on multiple SQL Servers December 31, 2015 by Daniel Calbimonte Introduction If you are a DBA and you have one single server with SQL Server to administer, backup the database is a simple task. However, there are several companies with hundreds and some with thousands of Servers with SQL Server. To handle multiple SQL Server backups at the same time is a nightmare. How can we backup the databases of multiple SQL Servers at the same time? In this article, we will show some solutions, but we will focus on a third party tool. This tool is the ApexSQL Backup. Requirements SQL Server 2005 or later (in this example, we are using SQL Server 2014) ApexSQL Backup There are several ways to backup multiple SQL Servers at the same time. In this chapter, we will show some possible solutions: Multi–server Administration This option is available in SQL Server 2005 and later versions. One solution is to have a Master Server and one or more target servers. The master server distributes the job to the target servers and you need to configure the SQL Server Agent in all the Servers. We will give more details about this option in future articles. Central Management Server This option is available in SQL Server 2008 and later versions. You need a central Server and a Group with the connections of the other SQL Servers. You can execute T-SQL queries to all the Servers of the group at once. We will give more details about this option in future articles. SQL Server Integration Services (SSIS) You could create a custom package in SQL Server with all the Server connections. This option could be more complicated depending on your SSIS experience. PowerShell You could run the query and have a list of connections in a variable. This option is even more complicated than the SSIS. However, each DBA has different needs and sometimes it is the best option (when you already have a bunch of scripts, experience and a lot of code in PowerShell). Create your own tools You could also create your own code in .NET, Java or other programming tool. This is as difficult as the PowerShell. Especially if you want the backups to run in parallel and not sequentially. Anyway, according to the situation, it can be an option. My advice is not to reinvent the wheel. Try to use the simplest solution. ApexSQL Backup This article will be based on this tool from ApexSQL. This is a new tool used to handle several SQL Server Backups at the same time. Let’s start it. Getting started Open the ApexSQL Backup and if they were not added before, add the SQL Servers. Figure 1. Adding SQL Servers Press the Policy manager icon. With the Policy manager, we will create a policy to generate a backup, which can be applied later for multiple servers. Figure 2. The policy manager In the backup policies Window, we will create a new one. Figure 3. Adding a new policy Specify a name for the Policy. It is possible also to choose a full backup for a complete backup or a differential backup that contains the data changed since the last full backup. The Transaction log backup contains the backups of the transaction logs only. It is recommended to backup your transaction log frequently to truncate the log file. Figure 4. The policy information We can then create the name of the backup set, the description, the destination file name and the destination folder. You can write the names manually or use the default parameters. The default parameters help you to automatically create the names bases on parameters values like the database, backup type, date and time. You will also select the destination of the file, which uses parameters by default like the date and time. Figure 5. Name and destination You also have options to backup like the copy only, which allows you to create a backup independent of other backups in a sequence plan. You can retain the backups for a specified number of days. You can also specify the option Do not check backup set expiration to avoid checking the expirations. There are also options to verify the backup, to checksum the backup and to continue on error. The checksum, helps to detect the page checksum. If there is a bad page checksum, the backup will stop unless the continue on error option is enabled. You can optionally compress the backup and encrypt it. Figure 6. Backup options The last step to create the policy is to schedule it. You can run the backup immediately or schedule it. You can also activate the option to receive a notification email on success or failure. Figure 7. Scheduling backup You can schedule the backup to run daily, once or weekly and monthly. You can also specify what time the backup will be performed. You can also specify the frequencies per day, specify the start, and end date. Figure 8. The schedule options Once the schedule is done, we are ready to use the policy. Figure 9. The policy finished To deploy the policy, click the Deploy policy icon. Figure 10. Deploying the policy Select the policy created on step 9. Figure 11. Choosing policy to deploy This section is the most important. In this section, you can choose the servers and databases. You can choose multiple servers and multiple databases at the same time. Figure 12. Servers and database You can save the backups in local disks or in shared network folder. Figure 13. Backup destinations Finally, you will receive a notification message of the successful and failure of the backups per servers. Figure 14. The final message Conclusion As you can see, the ApexSQL Backup is a straightforward tool. It is very easy to manage several backups for several servers and databases at the same time. You just first need to create a backup policy and specify the options of your preference and after that, you need to deploy your policy selecting the servers and databases where you want to apply them. References For more information, refer to these links: Manage multiple database backups across different SQL Server instances How to schedule a SQL Server backup SQL Server database backup encryption About Latest Posts Daniel CalbimonteDaniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.View all posts by Daniel Calbimonte Latest posts by Daniel Calbimonte (see all) How to email SQL query results to your smartphone using the sp_send_dbmail stored procedure - July 24, 2017 How to copy an Azure SQL database using the Azure Portal, Cloud Shell and T-SQL - June 23, 2017 How to create an Azure SQL Database using the Cloud Shell - May 23, 2017 See also SQL Server database auditing techniques Auditing SELECT statements on SQL Server HIPAA compliance for SQL Server DBAs Related posts: How to execute jobs on multiple SQL Servers Backup SQL Server database to and restoring from multiple files SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances How to run multiple queries using the Central Management Server What is backup and restore in SQL Server disaster recovery?