PowerShell Script to Backup SQL Server Databases

Database is a critical part of any application and it is essential to maintain regular backup of your databases. In this article, we will demonstrate how can you use PowerShell script to backup the Microsoft SQL server databases and automate the backup using Windows Scheduler.

SQL server management studio is an environment managing any local or remote SQL server. You can also backup and restore any database using it. However if you wish to automate the regular database backup, you can manage it using PowerShell script.

In this tutorial, we will provide you step by step guideline to setup a PowerShell script and configure Windows scheduler for regular backup interval. We will also use 7-Zip command line tool to compress and archive the backup file.

PowerShell Script to Backup SQL Server Databases

To create a PowerShell script that backs up the SQL server database and archive it using 7-Zip, follow the below steps:

Step 1: Install the necessary software and cmdlet

1. Ensure that SQL Server Management Objects (SMO) and SQL server cmdlet are installed. You can install them using the following command in PowerShell:

Install-Module -Name SqlServer

2. Install 7-Zip on your system if it is not installed.

Step 2: Backup Script

1. Open any Text editor and paste the following PowerShell script code:

# Load SQL Server cmdlets
Import-Module SqlServer
 
# Set variables
$ServerInstance = "SQL-server-name-or-address"
$DatabaseName = "YourSQLDatabaseName"
$BackupDir = "C:\Backups\Temp"
$ArchiveDir = "C:\Backups"
$Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$BackupFile = "${BackupDir}\${DatabaseName}_${Timestamp}.bak"
$ArchiveFile = "${ArchiveDir}\${DatabaseName}_${Timestamp}.7z"
 
# Create backup and archive directories if they don't exist
if (!(Test-Path $BackupDir)) {
    New-Item -ItemType Directory -Path $BackupDir
}
if (!(Test-Path $ArchiveDir)) {
    New-Item -ItemType Directory -Path $ArchiveDir
}
 
# Backup the SQL Server database
Backup-SqlDatabase -ServerInstance $ServerInstance -Database $DatabaseName -BackupFile $BackupFile
Write-Host "Database backed up successfully."
 
# Archive the backup using 7-Zip
& "C:\Program Files\7-Zip\7z.exe" a -t7z $ArchiveFile $BackupFile
Write-Host "Backup archived successfully."
 
# Remove the original backup file
Remove-Item $BackupFile
Write-Host ".bak file is removed."

2. Please ensure that you update the variables as per your requirement.

3. Save the script with a .ps1 file extension, such as DBBackup-abc.ps1.

Step 3: Execute PowerShell Script manually

Open your PowerShell console and go to the path where you have saved the above script. Execute your script using the following command:

.\DBBackup-abc.ps1

The script will load the necessary modules, backup the database and compress it using 7-zip. Once the script is executed, it stores the backup file in .7z format in the path specified in $ArchiveDir variable.

Windows Task Scheduler to execute PowerShell Backup script

Our script is now ready and we can use Windows Scheduler to backup the database on the regular interval by following the below stpes.

1. Open Windows Task Scheduler by searching Task Scheduler in Windows search.

2. Click on “Create Task…” from left hand Action pane. This will open a popup window.

3. Enter the name of your task and set other option as per your requirements. For most use cases, default options work.

4. Go to Triggers tab and click on “New…” button. Select the trigger’s time, adjust other option as per your requirement and click Ok button.

5. Go to Actions tab and click on “New…” button. Set the following parameters:

Action: Start a program
Program/script: powershell.exe
Add arguments (optional): -ExecutionPolicy Bypass C:\Backup\scripts\DBBackup-abc.ps1
Start in (optional): C:\Backup\scripts\

Here, in “Add arguments (optional)” value, you need to specify the full path and script name where your PowerShell script is saved. Click Ok button and save your scheduler. You can manually run the scheduler to check whether it is working as expected or not.

You can search for the other knowledge base articles or join our web hosting forum to ask your questions to the community.