If you’ve ever struggled with configuring automated, scheduled backups for a SQL Express server, then check out Brian Knight’s super handy scripts.
The script contains three files:
- BackupExpress.sql – Does the bulk of the work, backing up every database on the instance other than tempdb.
- BackupExpress.cmd – The batch file that executes the script. Must pass in the instance name.
- ScheduleBackups.cmd – Schedules the job by using AT. Must pass in the instance name to schedule like ScheduleBackups.cmd .\SQLExpress.
To do this, make sure you have the Task Scheduler service started. Uncompress all three of the files into the root of your C drive and run the schedule file from the command prompt.
I tried to keep the solution simple since most people that have SQL Express on their workstation may not be technical. So with that said, there is very little configuration or options. The backup solution keeps 7 days of history and constantly overwrites the previous week’s backup.
Grab the scripts from the full blog post.
Don’t forget to add the Instance name of your SQL Express Server when you run ScheduleBackups.cmd from the command line, like so: