Setting Up Automatic Scheduled Sql Server Database Backups using PowerShell script: Supports SQL Server Express Edition also

One of my project was using SQL Server Express Edition which does not allow SQL Jobs to be created, so I need to find an alternative solution for setting up automatic scheduled Sql Server Database Backups. In this post, I will explain how that can be achieved in few steps using simple PowerShell script.

Note: For Part II of this post refer Zipping the database backups & Backup auto cleanup / Retention policy enforcement using PowerShell

Step 1: Open “Windows PowerShell” Terminal & Install the SQL Server PowerShell module

Install-Module -Name SqlServer

Click “Yes” on the prompt to install the NuGet provider.

Click “Yes” on the prompt to install the Sql Server module from “PSGallery“.

Step 2 (optional): Only required, if you get the “Commands Already Available” Error as shown in below screenshot

To resolve the above error, install SQL Server PowerShell module with “AllowClobber” option. This will overwrite a previous version of the SqlServer module, if already available.

Install-Module -Name SqlServer -AllowClobber

Click “Yes” on the prompt to install the Sql Server module from “PSGallery“.

Step 3: PowellShell script for backing up SQL Server database objects using Backup-SqlDatabase cmdlet of Sql Server PowerShell module

Sample Script (DatabaseBackup.ps1): In this script, we are creating backups of two databases (DataBase1 & DataBase2) & storing the backup files in “D:\SqlBackups\” location. We have PowerShell’s Get-Date cmdlet to get a DateTime string that is used for database backup file naming.

$datetime = (Get-Date).ToString('MM-dd-yyyy-hh-mm-ss-tt');
$database1 = "D:\SqlBackups\DataBase1_" + $datetime + ".bak";
$database2 = "D:\SqlBackups\DataBase2_" + $datetime + ".bak";
Backup-SqlDatabase -ServerInstance KAPIL-KHANDELWA -Database DataBase1 -BackupFile $database1;
Backup-SqlDatabase -ServerInstance KAPIL-KHANDELWA -Database DataBase2 -BackupFile $database2;

Step 4: Use “Windows Task Scheduler” for executing the PowerShell script on daily at a given time.

Here:

Program: powershell

Arguments: -File C:\DatabaseBackup.ps1