By: Ahmad Yaseen | Comments (18) | Related: > Express Edition
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:
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:
Click finish and now your backup is fully scheduled without a SQL Server Agent job.
Next Steps
- Test out this approach with your SQL Server Express Edition instances in your environment.
- Make sure you have backups for all of your SQL Server instances - Read this tip.
- Check out the following resources:
About the author
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
