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?
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 tools. 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.
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte