Scheduling backup and maintenance for SQL Server Express
- Verba Support (Unlicensed)
- Diofasi, Aron (Unlicensed)
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:
|
@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:
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:
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:
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.
Configuration steps: recurring hourly transactional log backup
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:
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:
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.