Delete Old Backup Files in SQL Express with PowerShell

To delete old backup files in SQL Express, schedule a PowerShell script that removes files past a chosen age. SQL Server Express has no SQL Server Agent. It has no jobs and no maintenance plan cleanup task. Nothing removes old backups, so the disk fills a little more each night.

Why Express Needs a Script to Delete Old Backup Files

On Standard and Enterprise editions, a maintenance plan cleanup task deletes old backup files. Express doesn’t have that task. Express backups run from Windows Task Scheduler or a batch file instead, and neither knows what a backup is. The folder grows until a backup fails for lack of space.

The script below can delete old backup files on any edition, because it only looks at files. It has four safety features. It touches only the file extensions you list. It supports a dry run. It stays out of subfolders unless you ask. And it refuses to delete anything when one backup type has no recent file.

That last rule matters most. If the nightly full backup fails for a week, a plain cleanup keeps deleting old files until none are left. The script checks each extension on its own. Fresh log backups can’t hide a missing full backup.

The Script

This is a PowerShell script, not T-SQL. Save it as C:\Scripts\CleanOldBackups.ps1 and run it from PowerShell.

[CmdletBinding(SupportsShouldProcess)]
param(
    [Parameter(Mandatory)] [string] $Folder,
    [int] $DaysToKeep = 7,
    [string[]] $Extensions = @('.bak', '.trn'),
    [switch] $Recurse
)
if (-not (Test-Path -LiteralPath $Folder -PathType Container)) {
    Write-Error "Folder not found: $Folder"
    exit 2
}
$cutoff = (Get-Date).AddDays(-$DaysToKeep)
$files = @(Get-ChildItem -LiteralPath $Folder -File -Recurse:$Recurse |
    Where-Object { $Extensions -contains $_.Extension.ToLower() })
if ($files.Count -eq 0) {
    Write-Warning "No backup files in $Folder. Nothing was deleted."
    exit 1
}
foreach ($group in ($files | Group-Object { $_.Extension.ToLower() })) {
    if (-not ($group.Group | Where-Object { $_.LastWriteTime -ge $cutoff })) {
        Write-Warning "No $($group.Name) file from the last $DaysToKeep day(s) in $Folder. Nothing was deleted."
        exit 1
    }
}
$old = @($files | Where-Object { $_.LastWriteTime -lt $cutoff })
foreach ($f in $old) {
    Remove-Item -LiteralPath $f.FullName
}
"Older than {0:yyyy-MM-dd}: {1} file(s). Newer, kept: {2}." -f $cutoff, $old.Count, ($files.Count - $old.Count)

The $cutoff date is today minus the days to keep. The script groups the files by extension. If any group has no file newer than the cutoff, it stops. Otherwise it deletes the old files. SupportsShouldProcess is what makes -WhatIf work. PowerShell passes that switch down to Remove-Item, so a dry run prints each deletion and performs none.

The check looks at files, not at whether a backup finished. A zero-byte file from a failed backup counts as recent. Point -Folder at a folder that holds only backups. Add -Recurse only for a folder tree you know, because it removes every matching file below it.

Test It With -WhatIf

Build a test folder first. This PowerShell block creates five backup files and one text file. It sets the backups to 12, 9, 8, 3 and 1 days old, and the text file to 30. It isn’t T-SQL.

$folder = 'D:\SqlExpressBackups'
New-Item -ItemType Directory -Force -Path $folder | Out-Null
$ages = [ordered]@{ 'Sales_full_1.bak' = 12; 'Sales_full_2.bak' = 9; 'Sales_log_1.trn' = 8; 'Sales_full_3.bak' = 3; 'Sales_log_2.trn' = 1; 'notes.txt' = 30 }
foreach ($name in $ages.Keys) {
    $path = Join-Path $folder $name
    Set-Content -Path $path -Value 'test'
    (Get-Item $path).LastWriteTime = (Get-Date).AddDays(-$ages[$name])
}

Always start with a dry run on a real folder. The command is powershell -NoProfile -ExecutionPolicy Bypass -File C:\Scripts\CleanOldBackups.ps1 -Folder D:\SqlExpressBackups -DaysToKeep 7 -WhatIf.

The dry run printed the next block. It is output, not code to run.

What if: Performing the operation "Remove File" on target "D:\SqlExpressBackups\Sales_full_1.bak".
What if: Performing the operation "Remove File" on target "D:\SqlExpressBackups\Sales_full_2.bak".
What if: Performing the operation "Remove File" on target "D:\SqlExpressBackups\Sales_log_1.trn".
Older than 2026-09-29: 3 file(s). Newer, kept: 2.

Three files are past the 7 day limit. The two newer backups stay, and the text file is never listed. Run the same command without -WhatIf and those three files go. The final line then reports what was deleted.

Now the safety rule. Ask for a 1 day limit when the newest full backup is 3 days old. The script prints the next warning. It is output, not code to run.

WARNING: No .bak file from the last 1 day(s) in D:\SqlExpressBackups. Nothing was deleted.

The script exits with code 1 and leaves every file alone.

The same happens in a mixed folder. Take a full backup that is 10 days old and a log backup that is 1 day old. Use a limit of 7 days. A script that counted all files together would see the fresh log backup. It would delete the only full backup. This one warns about .bak and deletes nothing.

Task Scheduler shows the exit code in its Last Run Result column as 0x1. Check that column, or alert on it.

Take the Backup in the Same Task

Task Scheduler can run two actions in order. The first takes the backup and the second cleans up, so the safety rule always sees a fresh file. The backup is plain T-SQL. This one builds a dated file name in the instance’s default backup folder.

IF DB_ID(N'ExpressBackupDemo') IS NULL CREATE DATABASE ExpressBackupDemo;
GO
DECLARE @file nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultBackupPath'))
    + N'\ExpressBackupDemo_' + FORMAT(SYSDATETIME(), N'yyyyMMdd_HHmmss') + N'.bak';
BACKUP DATABASE ExpressBackupDemo TO DISK = @file WITH INIT, CHECKSUM;

Save a script like that as a .sql file with your own database name. Run it with sqlcmd -S .\YourInstance -E -C -i "C:\Scripts\BackupDb.sql". The -E switch uses your Windows login. The -C switch trusts the server certificate, so drop it if your instance has a trusted one.

The file name carries the date, and the file’s write time carries the same date. The cleanup script reads the write time. SQL Server also records the file in msdb, which this query shows.

SELECT bs.database_name, RIGHT(bmf.physical_device_name, CHARINDEX(N'\', REVERSE(bmf.physical_device_name)) - 1) AS BackupFile
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = N'ExpressBackupDemo';

Schedule It

In Task Scheduler, choose Create Task. On the General tab, pick Run whether user is logged on or not. Add a daily trigger at a quiet hour. On the Actions tab, add two actions in this order. The first is sqlcmd with the arguments above, for the backup. The second is powershell.exe, for the cleanup. The arguments for the second one are -NoProfile -ExecutionPolicy Bypass -File "C:\Scripts\CleanOldBackups.ps1" -Folder "D:\SqlExpressBackups" -DaysToKeep 7.

The account that runs the task needs delete rights on the backup folder. SQL Server’s own service account doesn’t matter here, because only PowerShell deletes files.

What the Script Doesn’t Do

Deleting a file doesn’t delete its history. SQL Server keeps a row in msdb for every backup. The Restore Database wizard slows down as the rows pile up. Clean them with sp_delete_backuphistory on a schedule too.

You could argue that a longer retention is safer than a script that deletes. It is, until the disk fills and the next backup fails. Pick a retention you can afford. Copy the newest backups off the machine as well. Then the script that deletes old backup files never touches your only copy.

What to Remember

Run with -WhatIf first. Take the backup before the cleanup in the same task. Keep at least one recent file of each type, so a broken backup can’t empty the folder. Run the cleanup script below when you finish with the demo database. Then schedule the script that will delete old backup files from your real folder.

USE master;
GO
IF DB_ID(N'ExpressBackupDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ExpressBackupDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ExpressBackupDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ExpressBackupDemo';

SQL Server doesn’t delete the backup file. Remove ExpressBackupDemo_*.bak from your default backup folder yourself.

A backup folder is not an archive, it is a window that has to keep moving.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.