The Express versions of Microsoft SQL Server are used very often. Most of the times it comes together with 3rd party applications. And for small tasks and databases it is a good choice, even for bussiness environments. Although you don’t want to get many servers with Expresss versions. Because all these needs to be managed.
On of the managment tasks that you need to do is to make backups of the databases on a regular bases. But unfortunatly the Express versions don’t have a builtin mechanism to make automated backups. This is because there is no SQL Agent in SQL Server Express. And you don’t want to do this manually everyday.
One option you have is to use SQLCMD with an SQL script. And then make a scheduled task of it. How you can do this is explained in this article by Jasper Smith. He also provides a stored procedure you can us. So you don’t have to write it yourself.
But personally I prefer the stand alone application ExpressMaint.exe that is also made by Jasper. It works the same as the SQLmaint utility that was part of SQL Server 2000. ExpressMaint.exe is easy to use and if you use it in a batch script you can make a scheduled task of it. In this article Jasper explains how to use it.
Both the stored procedure and the application can do more then only make backups. Just like the old SQLMaint utility the can be used to do:
- Full Database Backup
- Differential Database Backup
- Log Backup
- Housekeeping of backup files
- Database Integrity Checks
- Database Index Rebuilds
- Database Index Reorganization
- Database Statistics Update
- Report Creation