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.
- 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:
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.
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.
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.
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
- Functions and stored procedures comparisons in SQL Server - February 20, 2017
- Functions vs stored procedures in SQL Server - February 14, 2017
- How to generate random SQL Server test data using T-SQL - January 26, 2017
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.
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
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