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 sql_express_maintanance.sql 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: DB - Full Database Backup DIFF - Differential Database Backup LOG - Log Backup CHECKDB - Database Integrity Check REINDEX - Rebuild all indexes REORG - Reorganize all indexes
@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:

exec master.dbo.expressmaint
   @database      = 'verba',
   @optype        = 'DB',
   @backupfldr    = 'c:\backup',
   @reportfldr    = 'c:\report',
   @verify        = 1,
   @dbretainunit = 'days',
   @dbretainval   = 4,
   @rptretainunit = 'weeks',
   @rptretainval = 1,
   @report        = 1

In order to run expressmaint stored procedure, the following options have to be enabled in the Surface Area Configuration:

OLE Automation
xp_cmdshell

For SQL Server 2005:
On the Start menu, point to All Programs, Microsoft SQL Server, Configuration Tools, and then click SQL Server Surface Area Configuration. Select the Surface Area Configuration for Features Tool. Enable the OLE Automation stored procedures and the xp_cmdshell options.

For SQL Server 2008:
In Management Studio connect to a component of SQL Server. In Object Explorer, right-click the server, and then click Facets. In the View Facets dialog box, expand the Facet list, and select the appropriate Surface Area Configuration facet (Surface Area Configuration, Surface Area Configuration for Analysis Services, or Surface Area Configuration for Reporting Services). In the Facet properties area, set the OleAutomationEnabled and the XPCmdShellEnabled values to True. Click OK.

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:

Step 1 Open a text editor and copy the following code into it then save it to e.g. c:\backup_scripts\Verba_daily_full.sql. This 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:

exec master.dbo.expressmaint
   @database      = 'verba',
   @optype        = 'DB',
   @backupfldr    = 'c:\backup',
   @reportfldr    = 'c:\report',
   @verify        = 1,
   @dbretainunit = 'days',
   @dbretainval   = 4,
   @rptretainunit = 'weeks',
   @rptretainval = 1,
   @report        = 1

Step 2 Start the Task Scheduler by clicking on Start menu/All Programs/Accessories/System Tools/Task Scheduler.

Step 3 Click Create Basic Task to start the Scheduled Task Wizard.

Step 4 Type a name for the task.

Step 5 Choose Daily from the scheduling options.

Step 6 Click Next, specify the information about the time to run the task. Set Start time to an appropriate value when the load on the Verba system is low, set the Recur every option to 1 day, and then click Next.

Step 7 Choose Start a program from the task to perform list, and then click Next.

Step 8 Click Browse, browse to SQLCMD.exe (for SQL 2005 Servers by default it can be found in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn, for SQL 2008 Servers by default it can be found in C:\Program Files\Microsoft SQL Server\100\Tools\binn), and then click Open.

Step 9 Type the following content to the Add arguments text box, and then click Next:

-i "c:\backup_scripts\Verba_daily_full.sql"

Step 10 Select the checkbox to Open the Advanced Properties for this task and then click Finish.

Step 11 You must leave a space after the existing contents. The sql script file was saved in step 1.

Step 12 Click OK. If prompted, supply the password for the account again.

Step 13 Test the created task by right-click on it and select Run. A command prompt windows named SQLCMD has to be opened while the task is running. Check the created backup file and the log files. If you encounter any problem, try to run the task command manually in a command prompt window.

Make sure that you do not leave the backup files on the Verba server. It is highly recommended to move the backup sets to another server or network drive.

Configuration steps: recurring hourly transactional log backup

You should use these steps if recording is highly important for your organization. Otherwise a daily full database backup might be enough for your purposes.

In order to set transaction log backup the database recovery mode has to be set to full. To set the recovery model to full, refer to SQL Server backup and restore.

In addition to hourly transactional log backups, you have to define the daily full database backup in order to decrease the log files (after a full database backup, SQL Server truncates the transactional log backup automatically). To define the daily full database backup, refer to Configuring a recurring daily full database backup for Verba database in SQL Server 2005/2008 Express Edition.

The first transactional log backup can only be created, if at least one full database backup exists. To create a full database backup, refer to Creating a one-off full database backup. Alternatively, according to your full database backup scheduling settings, you can wait for the first full database backup creation (until that, the transactional log backups will not be created).

To configuring a recurring hourly transactional log backup for Verba database in SQL Server 2005/2008 Express Edition:

Step 1 Open a text editor and copy the following code into it then save it to e.g. c:\backup_scripts\Verba_hourly_log.sql. This will create a transactional log backup to c:\backup, verify the backup and report to c:\report keeping backups for 1 day and reports for 1 day:

exec expressmaint
   @database      = 'verba',
   @optype        = 'LOG',
   @backupfldr    = 'c:\backup',
   @reportfldr    = 'c:\report',
   @verify        = 1,
   @dbretainunit = 'days',
   @dbretainval   = 1,
   @rptretainunit = 'days',
   @rptretainval = 1,
   @report        = 1

Step 2 Start the Task Scheduler by clicking on Start menu/All Programs/Accessories/System Tools/Task Scheduler.

Step 3 Click Create Basic Task to start the Scheduled Task Wizard.

Step 4 Type a name for the task.

Step 5 Choose Daily from the scheduling options.

Step 6 Click Next, specify the information about the time to run the task. Set Start time to an appropriate value when the load on the Verba system is low, set the Recur every option to 1 day, and then click Next.

Step 7 Choose Start a program from the task to perform list, and then click Next.

Step 8 Click Browse, browse to SQLCMD.exe (for SQL 2005 Servers by default it can be found in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn, for SQL 2008 Servers by default it can be found in C:\Program Files\Microsoft SQL Server\100\Tools\binn), and then click Open.

Step 9 Type the following content to the Add arguments text box, and then click Next:

-i "c:\backup_scripts\Verba_hourly_log.sql"

You must leave a space after the existing content. Use the sql script file name that was saved in step 1.

Step 10 Select the checkbox to Open the Advanced Properties for this task and then click Finish.

Step 10 Select the Triggers tab and click on the Edit button. In the Advanced Schedule Options window enable the Repeat task every option then set the attribute to 1 hour. Finally set the Duration option to 2 hours. Click OK.

Step 11 Back in the main window, click OK. If prompted, supply the password for the account again.

Step 12 Test the created task by clicking on it using the right mouse button and select Run. A command prompt window named SQLCMD should be opened while the task is running. Check the created backup file and the log files. If you encounter any problem, try to run the task command manually in a command prompt window.

Make sure that you do not leave the backup files on the Verba server. It is highly recommended to move the backup sets to another server or network drive as soon as possible after the execution of the backup scripts.