Backups

A free SQL Server Express backup script with alerting

Express has no SQL Agent and no maintenance plans. Here's a single, auditable PowerShell script that does verified backups, encryption, off-site upload, retention — and tells someone when it breaks.

SQL Server Express powers an enormous number of small line-of-business servers — and ships with no SQL Agent, so there are no scheduled backups unless somebody builds them. The usual fixes are a paid backup product or a fragile one-liner in Task Scheduler that nobody ever hears from again. This is the third option: a free, single-file PowerShell script you can read before you run, proven on a production migration, with monitoring built in.

Download the kit (.zip)

Or read the files first (they're plain text — that's the point): windows-sqlserver.ps1 · example config · preflight.ps1 · all kits

What it does, exactly

  • Backs up every online database on the instance (system DBs excluded, plus any you list) with BACKUP DATABASE ... WITH CHECKSUM, then runs RESTORE VERIFYONLY on each — a backup that doesn't verify is counted as a failure, not quietly uploaded.
  • Archives the dumps plus any folders you list (web root, config, certificates) into an AES-256-encrypted 7z set with encrypted headers, split into volumes that survive flaky uploads.
  • Uploads off-site over FTP, FTPS or SFTP via the WinSCP .NET assembly, with a SHA-256 manifest for restore-time verification.
  • Applies retention remotely (weekly sets kept ~a month, first-of-month sets kept ~6 months) and locally.
  • Reports to a MonitorSpider backup monitor in its finally block: success with size and duration, or failure with the first errors — and if the script dies so hard it never pings, the monitor's expected-interval window catches that too. That last case — the backup that silently stops running — is the one that actually bites people.

The short version: run the wizard

Unzip the kit anywhere, double-click START-HERE.cmd (Open → Yes on Windows' two prompts), and paste your monitor's ping URL. The launcher exists because Windows blocks downloaded PowerShell scripts by default; it starts the wizard with the right settings, and the wizard moves itself to a permanent home, fetches the monitor's settings, detects your SQL instances, asks where to upload, generates an archive password if you want one, installs the portable tools, runs every pre-flight check, registers the weekly scheduled task, and offers to run the first backup immediately. That's the whole setup. The steps below are the same thing done by hand, for people who like to see the moving parts.

Setup by hand, in five steps

  1. Download the kit files into a folder, e.g. C:\server-backups\kit\.
  2. Copy the example config to backup-config.json and fill in: your instance (e.g. .\SQLEXPRESS), folders, a strong archive password (store it in your password manager — without it the backup is unreadable, which is the point), and the upload target.
  3. Create the monitor: in MonitorSpider, add a Backup (push) monitor, set the expected interval (weekly + a few hours grace), and paste its ping URL into the config. One backup monitor is included on the free plan.
  4. Run .\preflight.ps1. Check mode is read-only: it verifies PowerShell, the tools, the config, that sqlcmd can actually reach your instance (the ODBC 18 self-signed-certificate quirk surfaces here, not at 3am), that the upload port answers, and that the ping URL works — you'll see the monitor light up in your dashboard. Missing tools? .\preflight.ps1 -Install downloads portable copies of 7-Zip, the WinSCP assembly and Microsoft's go-sqlcmd into a tools\ folder — every download verified against SHA-256 hashes pinned in the script, no system-wide installs, no admin needed, and you can read exactly what it fetches before running it.
  5. Schedule it: a weekly Task Scheduler job running as SYSTEM (powershell.exe -NoProfile -ExecutionPolicy Bypass -File C:\server-backups\kit\windows-sqlserver.ps1). SYSTEM needs backup rights on the instance — it has them by default on Express.

Restoring (practice this once)

  1. Download all volumes of a set plus its .sha256 manifest.
  2. Verify: Get-FileHash *.7z.* against the manifest.
  3. Extract: 7z x yourserver-<date>-weekly.7z.001 -p<archive-password> (7-Zip finds the other volumes itself).
  4. Restore databases from the .bak files with SSMS or RESTORE DATABASE.

A backup you've never restored is a hypothesis. The size-over-time graph on your monitor helps here too: a set that suddenly shrinks is a restore test you should run today.

Already have a backup tool?

Keep it — just add the watchdog. Send-BackupPing.ps1 reports success/failure from any tool's post-job hook, and the Linux equivalent of this kit (files + MySQL/PostgreSQL) has the same ping built in.

Free script, free monitor, no credit card — create your backup monitor and stop hoping the event log gets read.
Keep reading