How to Use PowerShell Script to Backup Multiple SQL Databases
This tutorial presents how to create PowerShell script to backup multiple SQL databases step by step and offers powerful SQL backup software to make SQL database backup easier.
- About SQL Server PowerShell
- Create PowerShell script to backup multiple SQL databases
- Easier way to backup multiple SQL Server databases
- To sum up
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.
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
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
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?
The powerful SQL backup software - AOMEI Cyber Backup comes to help. You may take advantage of the following features when performing SQL Server backup:
Download the free trial of AOMEI Cyber Backup and explore all the wonderful features right now!
Preparations for SQL Server backup
1. Run the downloaded .exe file to install AOMEI Cyber Backup, and launch its main interface. Click Source Device -> Microsoft SQL -> Add Microsoft SQL.
2. Choose Download proxy program or Copy link to download the Agent program and install it on the device with SQL Server installed. Then, click Already installed proxy to select the device.
Next, click -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.
That's it, you can schedule data backup for the added SQL Server now.
Steps to backup multiple SQL databases via professional software
1. Click Backup Task -> Create New Task to launch the task creating page. Choose the Backup Type as Microsoft SQL Backup.
2. Click on Device Name to specify the SQL instance and databases for backup. You can select one or multiple databases as you need.
3. Select a Target to save your SQL backups - it could be a local path or network path.
4. Set up a Schedule to run the SQL database backup daily, weekly or monthly, and choose the backup method as Full, Incremental or Differential Backup.
Click Start Backup to execute the task. Once it's finished, you will find it in the Backup Task tab.
✍More useful features:
- Backup Cleanup helps you to delete older backup version automatically and therefore save storage space. You can upgrade AOMEI Cyber Backup to enjoy this advanced feature.
- Email Notification enables you to receive email notifications when the task is abnormal or successful.
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 Cyber Backup can do you a favor.