How to Use PowerShell Script to Backup Multiple SQL Databases?

About SQL Server PowerShell

PowerShell is a cross-platform task automation solution made up of a command-line shell, a scripting language, and a configuration management framework. Talking about SQL Server PowerShell, there are two modules:

• SqlServer module: it is the current PowerShell module to use. It contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.
• SQLPS module: it is included with the SQL Server installation (for backward compatibility) but is no longer updated.

SqlServer offers a variety of cmdlets to help with different tasks in SQL Server. To backup SQL Server database objects, you can use Backup-SqlDatabase (*learn more about Backup-SqlDatabase). Following are some commonly-used parameters:

• -BackupAction: specifies the type of backup operation to perform. Valid values are: “Database”, “Files” and “Log”.
• -BackupFile: specifies the location and file name of the backup. This is an optional parameter and it cannot be used with the BackupDevice or BackupContainer parameters.
• -ServerInstance: specifies the name of a SQL Server instance. This server instance becomes the target of the backup operation.
• -Database: specifies the name of the database to back up. This parameter cannot be used with the DatabaseObject parameter.

With these cmdlets, you can also use PowerShell script to backup multiple sql databases.

Create PowerShell script to backup multiple SQL databases

Please note that previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS. To use PowerShell with SSMS 17.0 and later, you need to install the SqlServer module from the PowerShell Gallery.

After installing the SqlServer module, you can follow the steps below to write PowerShell script to backup SQL Server databases:

1. Click the Start menu, select Windows PowerShell, and then click Windows PowerShell ISE.

Note: The Windows PowerShell Integrated Scripting Environment (ISE) enables you to run commands and write, test, and debug scripts in a single Windows-based graphic user interface.

2. In Windows PowerShell ISE, click New Script on the toolbar and type the following cmdlets to backup multiple SQL databases as per your need. In my case, I’m going to backup the database “TestDB” and “DemoDB”.

• Backup-SqlDatabase -ServerInstance "DESKTOP-J6AHKLA" -Database "TestDB" -Initialize
• Backup-SqlDatabase -ServerInstance "DESKTOP-J6AHKLA" -Database "DemoDB" -Initialize

✍ Tips:
① The cmdlets used above will create a complete database backup of the database “TestDB” and “DemoDB”, and the backup files will be saved to the default backup location of the server “DESKTOP-J6AHKLA”. For me, the backup location is:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup
② I only specify the server name here, which means to use the default instance on the server. You can specify a different instance by using -ServerInstance "Computer\Instance".
③ “-Initialize” means to overwrite any existing backup sets on the media and make this backup the first backup set on the media. If you omit this parameter, the backup file will contain multiple copies of the database after you run the PowerShell script for multiple times, thus the “.bak” file getting really large.

3. Click Run Script on the toolbar to backup SQL Server databases for the first time. You can view the backup progress and result under the Script Pane.

4. On the File menu, click Save As. Select your desired location to save the script file and enter a name for the file. Finally, click Save and exit Windows PowerShell ISE.

How to run PowerShell script the next time?

Method 1: Run the “.ps1” file with PowerShell

• Right-click on the “.ps1” file you saved before and select Run with PowerShell.
• You’ll be asked to change the execution policy. Type a (i.e. “Yes to All”) and press Enter to continue.

Method 2: Run script from Windows PowerShell ISE

• Launch Windows PowerShell ISE. Click Open Script on the toolbar, select the script file you created before and click Open.
• Click Run Script to begin backing up SQL databases.

How to create PowerShell script to backup all SQL databases?

The steps are the same as mentioned above. Just use the cmdlet below:

Get-ChildItem "SQLSERVER:\SQL\DESKTOP-J6AHKLA\DEFAULT\Databases" | Backup-SqlDatabase

✍ Tips:
◑ This command backs up all user-created databases on the server instance “DESKTOP-J6AHKLA\DEFAULT” to the default backup location. Each database has a backup file named [database name].bak.
◑ System databases are not backed up. You can specify the Backup-SqlDatabase cmdlet to backup the system database “master”, “model”, and “msdb” based on your need. Learn more about system database backup and restore.

Easier way to backup multiple SQL Server databases

As you see, you need to write the Backup-SqlDatabase cmdlet repetitively to backup several databases. If there are only a few databases that need to be backed up, PowerShell SQL backup is recommended. What if there are plenty of databases you’d like to backup? Is there a tool that can help you select multiple databases at once?

Powerful SQL backup software - AOMEI Centralized Backupper Database Edition comes to help. You may take advantage of its following features to perform SQL Server backup:

★ It is embedded with the SQL Server Backup feature that enables you to backup multiple instances and databases on one PC/server.
★ It offers the Schedule feature to help you run auto backups. You can perform SQL Server auto backup every day, every week, or every month.
★ It supports backing up databases on all newer SQL Server versions, including SQL Server 2019/2017/2016/2014/2012/2008(R2)/2005.

Download the free trial of AOMEI Centralized Backupper Database and explore all the wonderful features right now!

Preparations for SQL Server backup

1. Install AOMEI Centralized Backupper Database on one of the computers within your company. It will be used as the central management computer, and it can be a computer running either Windows PC OS or Windows Server OS.

2. Launch Centralized Backupper Database and go to Computers > Uncontrolled Computers > Install client program to install the client program on the computer with SQL databases you want to backup.

3. Request control of the client computer.

Steps to backup multiple SQL databases via Centralized Backupper Database

1. Go to Tasks tab, click New Task and select SQL Server Backup.

2. In the pop-up window, edit task name based on your need. Then, click Add Computers in the “Step 1” column and select the controlled computer with SQL Server databases.

3. Click the “Step 2” column and then click Add. The program will detect all SQL Server instances on the computer. Select all the databases you want to backup and click OK.

4. Click the “Step 3” column and then click Add Storage. Here you can add a network share or NAS path to store the backup files.

5. Do more configurations with Settings and Schedule as necessary. Confirm the operation and click Start Backup. Then, choose “Only create the task” to run SQL backup later or “Create and execute tasks” to run SQL backup immediately.

To sum up

Now you know how to write PowerShell script to backup multiple SQL databases. However, it is only suitable for backing up a small number of databases. If you have plenty of databases on a SQL Server that need to be backed up, AOMEI Centralized Backupper Database Edition can do you a favor.

All operations in AOMEI Centralized Backupper Database are simple thanks to the concise interface. What’s more, you can use it to backup multiple Windows Servers simultaneously. Try it now to discover more!