Automate SQL Server Express Backups and Purge Old Backups

Problem

SQL Server Express Edition serves many line-of-business systems as a backend database engine. Being a SQL Server DBA, you should make sure that all your database servers are regularly backed up in an automated way. Unfortunately, SQL Server Express Edition does not include SQL Server Agent in order to schedule the SQL Server database backups. How could we automate the backups for SQL Server Express Edition and make sure the old backup files are deleted?

Solution

We can use PowerShell with Windows Task Scheduler to write an automated backup and cleanup script for the SQL Server Express Edition backups.

It is a simple piece of code that you need to run after specifying the following parameters:

$serverName : The SQL Server name.

$backupDirectory : The path where you will keep the backup file.

$daysToStoreBackups : The number of days to keep the backup file in the backup folder.

The code will check the .BAK files in the backup directory and delete the files older than the ($daysToStoreBackups). Then it will loop through the user databases on your SQL Server and issue backups:

$serverName = "localhost"
$backupDirectory = "D:\backupSQL"
$daysToStoreBackups = 0
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
 
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
           $dbName = $database.Name      
        
            $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
            $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"
 
            $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
            $smoBackup.Action = "Database"
            $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
            $smoBackup.BackupSetName = $dbName + " Backup"
            $smoBackup.Database = $dbName
            $smoBackup.MediaDescription = "Disk"
            $smoBackup.Devices.AddDevice($targetPath, "File")
            $smoBackup.SqlBackup($server) 
            "backed up $dbName ($serverName) to $targetPath"
               
}

In order to schedule this script to be run automatically, you need to save it as a .ps1 file. Then from the Task Scheduler create a task using Create Basic Task as follows:

In order to schedule this script to be run automatically, you need to save it as a .ps1 file.

Going through the basic steps for naming and scheduling that task, we will choose to “Start a program” once we are asked to specify what this task will perform.

In the Edit Action window, we will choose which program we will run in this task. In the program field, specify the path for the PowerShell EXE (C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe).

Then in the argument field specify the path and name for the backup PowerShell script (C:\ScheduledTasks\SQL_Express_Job.ps1) as shown below:

Then in the argument field specify the path and name for the backup PowerShell script (C:\ScheduledTasks\SQL_Express_Job.ps1)

Click finish and now your backup is fully scheduled without a SQL Server Agent job.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *