posted 2011-07-24
There's not too much background to this one. Microsoft offers a free version of its SQL Server product, called SQL Server Express. However,
There is an awesome utility called ExpressMaint which gives you access to most of these backup features; you just have to script it yourself.
Most of the documentation you'd need can be found in this SQLdbatips article. For my purposes, I keep backups of all databases going back two days, with each database having its own backup directory. If this is acceptable to you, you can just use my sql_express_backup.bat batch file:
REM Nightly backup script utilizing the ExpressMaint.exe utility. REM REM -D ALL_USER -T DB -B %backup_dir%\%%x: REM Backup all user databases to backup_dir\instance_name. REM REM -BU DAYS, -BV 2: REM Keep backups for 2 days. REM REM -V: Verify the backup file. REM -C: Continue on error. REM REM The carat character '^' allows you to split one command onto REM multiple lines! Who knew? REM REM The path to the ExpressMaint.exe executable. SET express_maint="C:\path\to\ExpressMaint.exe" REM The names of the instances you'd like to back up. SET instances=SQLEXPRESS SHAREPOINT REM The directory in which you'd like to store the backups. REM Subdirectories will be created for each instance. SET backup_dir=C:\path\to\backups for %%x in (%instances%) do ( %express_maint% ^ -S %%x ^ -D ALL ^ -T DB ^ -B "%backup_dir%\%%x" ^ -BU DAYS ^ -BV 2 ^ -V ^ -C )
There are a few variables you'll want to modify near the top of the
file, namely, express_maint
, instances
, and
backup_dir
.
You'll probably also want to schedule it to run every night, but that can be accomplished through the standard “Scheduled Tasks” thing.