How to Use PowerShell Script to Backup Multiple SQL Databases
About SQL Server PowerShell
PowerShell is a cross-SQL Server PowerShell is a platform task automation solution made up of a command-line shell, a scripting language, and a configuration management framework. It includes two primary modules: SQLPS and SQL Server PowerShell. SQLPS is the command-line shell and scripting language, providing a way to automate tasks and interact with SQL Server.
- SqlServer module: The current PowerShell module to use for SQL Server is the one that contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.
- SQLPS module: The SQL Server 2005 Express Edition is included with the SQL Server installation (for backward compatibility) but is no longer updated.
SqlServer provides various cmdlets for different tasks, including backing up SQL Server database objects. The Backup-SqlDatabase cmdlet can be used for this purpose, and it offers several commonly-used parameters.
- The BackupAction specifies the type of backup operation to perform, which can be either "Database", "Files", or "Log".
- The BackupFile parameter specifies the location and file name of the backup, and is an optional parameter that cannot be used with the BackupDevice or BackupContainer parameters.
- The ServerInstance property specifies the name of a SQL Server instance, which becomes the target of the backup operation.
- The Database parameter specifies the name of the database to back up, and cannot be used in conjunction with the DatabaseObject parameter.
You can use the SQL Server cmdlets in PowerShell to backup multiple SQL databases. The Backup-SqlDatabase cmdlet allows you to specify a database to backup, while the Backup-SqlDatabase -BackupFile cmdlet enables you to specify a file path for the backup.
Create PowerShell script to backup multiple SQL databases
The SqlServer module was previously included with SQL Server Management Studio (SSMS), but only in versions 16.x. 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 write a PowerShell script to backup SQL Server databases by importing the module, getting the list of databases on the SQL Server instance using `Get-SqlDatabase`, and then backing up each database using `Backup-SqlDatabase` with the desired parameters. You can also specify the backup file location for all databases using the `-BackupFile` parameter.
1. To start Windows PowerShell ISE, click the Start menu, select Windows PowerShell, and then click Windows PowerShell ISE.
Note: The Windows PowerShell Integrated Scripting Environment (ISE) is a graphical user interface that allows you to run commands, write, test, and debug scripts all within a single Windows-based environment.
2. In Windows PowerShell ISE, click "New Script" on the toolbar and type the following cmdlets to backup multiple SQL databases. For example, to backup the "TestDB" and "DemoDB" databases, you would enter the necessary commands.
- Backup-SqlDatabase -ServerInstance "DESKTOP-J6AHKLA" -Database "TestDB" -Initialize
- Backup-SqlDatabase -ServerInstance "DESKTOP-J6AHKLA" -Database "DemoDB" -Initialize
✍ Tips:
① The cmdlets create a complete database backup for "TestDB" and "DemoDB" and save the backup files to the default backup location on server "DESKTOP-J6AHKLA", which is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup. The server name specifies the default instance, and you can use -ServerInstance "Computer\Instance" to specify a different instance. The -Initialize parameter overwrites any existing backup sets on the media, making this backup the first set, and omitting it would result in large ".bak" files with multiple database copies after running the script multiple times.
3. To backup SQL Server databases for the first time, click on the "Run Script" button on the toolbar. This will initiate the backup process, and you can monitor its progress and outcome in the Script Pane.
4. To save a script in Windows PowerShell ISE, click on the "File" menu, select "Save As," choose a location and name for the file, and then click "Save."
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" to execute the script.
- You'll be prompted 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, select the script file, 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:
◑ The command backs up all user-created databases on the server instance "DESKTOP-J6AHKLA\DEFAULT" to the default backup location, creating a backup file for each database named [database name].bak.
◑ System databases are not backed up by default, but you can use the Backup-SqlDatabase cmdlet to specifically back up the system databases "master", "model", and "msdb". This allows you to manually create backups of these critical system databases.
Easier way to backup multiple SQL Server databases
You can use the Backup-SqlDatabase cmdlet repetitively to backup several databases, but for a large number of databases, PowerShell SQL backup is recommended. To automate the process for multiple databases, you can use a SQL Server Management Studio (SSMS) tool that allows you to select and backup multiple databases at once, eliminating the need to run the cmdlet repetitively. This approach streamlines the backup process for a large number of databases.
Qiling Backup, a powerful SQL backup software, is here to help. With its features, you can easily perform SQL Server backup, taking advantage of its capabilities to ensure the security and reliability of your database.
★ The SQL Server Backup feature allows you to backup multiple instances and databases on PCs/Servers, and it also offers a schedule feature to automate database backups, enabling you to perform backups daily, weekly, or monthly.
★ The software supports backing up databases on all popular SQL Server versions, including SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 (R2), and 2005.
Download the free trial of Qiling Backup and explore all the wonderful features right now!
Preparations for SQL Server backup
I've scheduled a data backup for the added SQL Server. The backup will run automatically at the specified time.
Steps to backup multiple SQL databases via professional software
✍More useful features:
- Backup Cleanup Upgrading Qiling Backup allows you to automatically delete older backup versions, freeing up storage space.
- Email Notification The feature allows you to receive email notifications when a task is abnormal or successful.
To sum up
To backup multiple SQL databases, you can write a PowerShell script. However, this method is only suitable for a small number of databases. For a large number of databases, a more efficient solution is to use Qiling Backup, which can automate the backup process for you.
Related Articles
- Schedule Database Backup in SQL Server 2012/2014/2016/2019
You will learn how to schedule database backup in SQL server 2012/2014/2016 using SQL Server Agent jobs and SQL Serve Maintenance Plans. Also, an easier way is included. - Backup SQL Server Database and Restore to Another Server | Guide
Here's a simple guide to backup SQL Server databases and restore to another server. In addition, you can also migrate databases via Copy Database Wizard, or try a centralized backup solution. Keep reading to learn the details. - Top 3 Ways to Run MSSQL Backup in SQL Server 2012/2017/2019
Microsoft SQL Server (MSSQL) is widely used in enterprise deployments. If you are in need of backing up MSSQL databases, read this page to get the top 3 ways.