Daniel Calbimonte

How to run a backup on multiple SQL Servers

December 31, 2015 by

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?

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

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)

Related posts:

  1. How to execute jobs on multiple SQL Servers
  2. Backup SQL Server database to and restoring from multiple files
  3. Multiple methods for scheduling a SQL Server backup automatically
  4. SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
  5. How to run multiple queries using the Central Management Server
Backup and restore

About Daniel Calbimonte

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

168 Views