Howto: Schedule SQlServer Express backup

I was assigned with the task of maintaining a VPS server that hosts our bugtracking system and internal management software.

As there was a new version of our bugtracking system (fogbugz) available I was asked to update it.

The update process is very simple, it has an installer and you just have to click, next, next and finish, but as I don't like doing anything without backing up first, and as our SQLServer doesn't have a daily backup set up (aside from the full system backup, which I particularly don't trust), so I decided to set up a daily backup.

The first problem was figure out how sqlserver backup works, I found this nice article: Automating Database maintenance in SQL 2005 Express Edition Part II.

It worked flawlessly, but there was a problem, each backup took more than 1.5GB, we don't have much disk space and in my opinion keeping just one daily backup is as useless as no backup at all. I had to get my hands dirty and write a batch script to compact the backup and delete the backup files older than 7 days.

Compacting the files wasn't a problem, as winrar comes with a command line utility (rar.exe) it was easy to set up a command line to compact the backup files, just using rar.exe /? is enough to find out how to compact a set of files in command line, which was basically:

rar.exe a file.rar c:\FilesToAdd\*.* -r

But the real problem was how to delete the backups older than 7 days, I found several solutions in google, but none worked as I expected, some used vbscripts, other used batch scripts (which proved to be very dangerous), but none solved my problem, so I decided to write my own application to delete files older than 7 days, and in less than 10 minutes my problem was solved.

The application to download files older than X days can be found here.