Automate backups of SQL Express database

We have a workorder tracking system that runs on SQL Express 2005 edition. One of the many limitations of SQL Express, is its lack of the SQL Agent found on full versions of SQL, which allows automated tasks to be scheduled, one being scheduled backups.

So to work around this limitation, I created a scheduled task in Windows, which runs the following commands from a batch file to automatically backup the database:

osql -E -S SERVER\SQLINSTANCE -Q "BACKUP DATABASE [DATABASENAME] TO  [BACKUP_DEVICE] WITH  RETAINDAYS = 7, NOFORMAT, NOINIT,  NAME = N'DATABASENAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

osql -E -S SERVER\SQLINSTANCE -Q "BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY"

osql -E -S SERVER\SQLINSTANCE -Q "DBCC SHRINKDATABASE (DATABASENAME, TRUNCATEONLY)"

OK, so one thing to do before you go out and use this, is you need to create a "Backup Device", which you can do with SQL Management Studio Express. If you need a copy, just google it; it is a free download from Microsoft. The second and third commands are for basic DB maintenance, but it doesn't hurt to run them as well, at the same time.

That's about it, just edit the commands above, set your retention period (RETAINDAYS = x), put in a batch file, and create a scheduled task with your time requirements.

Email me if you have any questions or suggestions.

--Rick Estrada