如何在多个SQL Server上运行备份

介绍 ( 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.

如果您是DBA，并且只有一台服务器与SQL Server一起管理，则备份数据库是一项简单的任务。 但是，有几家公司拥有成百上千的服务器，有些则拥有数千台带有SQL Server的服务器。 同时处理多个SQL Server备份是一场噩梦。

How can we backup the databases of multiple SQL Servers at the same time?

我们如何同时备份多个SQL Server的数据库？

There are several ways to backup multiple SQL Servers at the same time. In this chapter, we will show some possible solutions:

有几种方法可以同时备份多个SQL Server。 在本章中，我们将显示一些可能的解决方案：

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.

此选项在SQL Server 2005和更高版本中可用。 一种解决方案是拥有一台主服务器和一个或多个目标服务器。 主服务器将作业分发到目标服务器，并且您需要在所有服务器中配置SQL Server代理。 我们将在以后的文章中提供有关此选项的更多详细信息。

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 2008和更高版本中可用。 您需要一个中央服务器和一个具有其他SQL Server连接的组。 您可以一次对组中的所有服务器执行T-SQL查询。 我们将在以后的文章中提供有关此选项的更多详细信息。

SQL Server Integration Services (SSIS)

小号 QL 小号 erver 我 ntegration 小号 ervices（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.

您可以在具有所有服务器连接SQL Server中创建自定义程序包。 根据您的SSIS经验，此选项可能更复杂。

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).

您可以运行查询，并在变量中具有连接列表。 该选项比SSIS还要复杂。 但是，每个DBA都有不同的需求，有时这是最好的选择（当您在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.

您还可以在.NET，Java或其他编程工具中创建自己的代码。 这与PowerShell一样困难。 特别是如果您希望备份并行而不是顺序运行。 无论如何，根据情况，可以选择。 我的建议是不要重新发明轮子。 尝试使用最简单的解决方案。

翻译自: https://www.sqlshack.com/how-to-run-a-backup-on-multiple-sql-servers/