Scheduling backup and maintenance for SQL Server Express
Database backup with SQL Server Express Edition
SQL Server 2005/2008 Express Edition does not contain maintenance plans and SQL Server Agent service.
There are two parts of the backup solutions for Express Edition:
instead of the maintenance plans:
Verba provides a stored procedure, called expressmaint (comes installed with the product)instead of the SQL Server Agent:
we recommend Windows Task Scheduler to run the maintenance tasks
In this document we explain how to use these two parts to create you database backup solution.
Installing the stored procedure for maintenance
You can install the stored procedure by executing the file on your SQL Server (on the Master DB, system databases/master) with a user that has sysadmin rights.
This will install expressmaint stored procedure provides the following features:
Full Database Backup
Differential Database Backup
Log Backup
Housekeeping of backup files
Database Integrity Checks
Database Index Rebuilds
Database index Reorganization
Report Creation
Stored procedure parameters
The following table describes the available parameters in this stored procedure:
Parameter | Required | Default | Description |
|---|---|---|---|
@database | Yes | None | The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases and ALL_SYSTEM which will process all system databases |
@optype | Yes | None | The type of maintenance operation to be performed. Valid values are:
|
@backupwith | No | Null | Specify additional backup options as documented in BOL for the BACKUP WITH command |
@backupfldr | No | Null | The base folder to write the backups to. Sub folders will be created for each database |
@verify | No | 1 | Indicates whether to verify the backup file. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE |
@verifywith | No | Null | Specify additional verify options as documented in BOL for the VERIFY WITH command |
@dbretainunit | No | Null | The unit of measure for the @dbretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old backup files are kept |
@dbretainval | No | 1 | The time period or number of copies of old backups to keep |
@report | No | 1 | Indicates whether to produce a report of the maintenance carried out. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE |
@reportfldr | No | Null | The folder where maintenance reports are written to if @report = 1 |
@rptretainunit | No | Null | The unit of measure for the @rptretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old reports are kept |
@rptretainval | No | 1 | The time period or number of copies of old reports to keep |
@checkattrib | No | 0 | Indicates whether to check the archive bit on a backup file before deleting it. This is a safety check to prevent deletion of files that have not been backed up onto tape. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE |
@delfirst | No | 0 | Indicates whether to delete old backups prior to doing the current backup. This is not advisable but can be useful if disk space is limited. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE |
@debug | No |
| Indicates whether print out debug information such as the commands generated and the contents of the temporary tables used in the procedure. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE |
How to use the stored procedure
For example calling the stored procedure with the following parameters will create a full verba database backup to c:\backup, verify the backup and report to c:\report keeping backups for 4 days and reports for 1 week:
Configuration steps: recurring daily full database backup
To configuring a recurring daily full database backup for Verba database in SQL Server 2005/2008 Express Edition: