You are here:
SQL Server 2012 - Microsoft's latest database engine SQL Server 2012 - Microsoft's latest database engine

Automating Database Backup for Microsoft SQL 2005 Express

If you are running a Microsoft SQL 2005 Express edition database and want to automate a daily backup of your database data, then this handy script can help you with this task and protect your data files.

The script was created by Jasper Smith and is a TSQL stored procedure. The procedure is called expressmaint. Copy the code from the link and run the stored procedure creation against the "master" database.

Once you receive a "stored procedure created successfully" message, you are free to begin using this to backup individual databases or a whole server of databases and data files. Full information can be found on SQL DBA Tips - Automating Database maintenance in SQL 2005 Express.

A quick example of using the script to backup your whole database server, keep the backup files for 1 week, reporting off and verify the data is shown below:

exec expressmaint @database = "ALL_USER", @optype = "DB", @backupfldr = "c:\sqlbackup", @verify = 1, @dbretainunit = "weeks", @dbretainval = 1, @report = 0