M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

Scheduling Backups for SQL Server 2005 Express Created: WY 6/16/08

I. Overview SQL Server 2005 Express does not come with a task agent that allows for the scheduling of backups. As a result, an alternate method must be used in order to schedule backups when using SQL Server 2005 Express. SQL Server 2005 Express backups may be scheduled through the use of a command line utility called “ExpressMaint” and using the Windows Task Scheduler to run a batch file. Please see this link http://www.sqldbatips.com/showarticle.asp?ID=29#related for additional information on the ExpressMaint utility, including a link to download. A copy of the utility is available for ProTrak personnel at O:\Microsoft\SQL Server\SQL_2005_Express\ExpressMaint. Setting up a scheduled backup involves 2 steps: 1.

Set up a batch file to allow ExpressMaint to be run using the desired switches/arguments.

2. Set up a task to run the batch file using the Windows Task Scheduler at a set time with appropriate rights.

II. ExpressMaint Utility Command line summary: Switch -? -S -D

Argument N Y Y

Required N Y Y

-T

Y

Y

Description Displays help for the ExpressMaint utility The SQL Server for ExpressMaint to connect to The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases, ALL_SYSTEM which will process all system databases and ALL which will process all databases The type of maintenance operation to be performed. Valid values are

     

DB - Full Database Backup DIF - Differential Database Backup LOG - Log Backup CHECKDB - Database Integrity Check REINDEX - Rebuild all indexes REORG - Reorganize all indexes

-B -V

Y N

N N

The base folder to write the backups to. Sub folders will be created for each database Indicates whether to verify the backup file

-BU

Y

N

-BV -R

Y Y

N N

The unit of measure for the -BV argument. Valid values are minutes, hours, days, weeks. The combination of these two arguments determines for how long old backup files are kept. Required if -B argument is specified. The time period of old backups to keep. Required if -B argument is specified. The folder where maintenance reports are written to

-1-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc -RU

Y

N

-RV -A

Y N

N N

-DS

N

N

-TO

Y

N

The unit of measure for the -RV argument. Valid values are minutes, hours, days, weeks. The combination of these two parameters determines for how long old reports are kept. Required if -R argument is specified. The time period or number of copies of old reports to keep. Required if -R argument is specified. 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. If this switch is specified then the timestamp for backup and report files is appended to the front of the filename (the default is to append to the end of the filename) This switch allows the setting of a statement timeout specified in minutes (e.g. -TO 10). If not specified, the default is 10 minutes.

Example Syntax (Note that the entire command should be on one line.): 1) Full Database Backup of all user databases to c:\backups, verify the backups and report to c:\reports keeping backups for 1 day and reports for 1 week expressmaint -S (local) -D ALL_USER -T DB -R c:\reports -RU WEEKS -RV 1 -B c:\backups -BU DAYS -BV 1 -V

2) Full Database Backup of all system databases to c:\backups, verify the backups and report to c:\reports keeping backups for 1 week and reports for 1 week expressmaint -S (local) -D ALL_SYSTEM -T DB -R c:\reports -RU WEEKS -RV 1 -B c:\backups -BU WEEKS -BV 1 -V

3) Log Backup of all user databases to c:\backups, don't verify the backups and report to c:\reports keeping backups for 1 day and reports for 1 day expressmaint -S (local) -D ALL_USER -T LOG -R c:\reports -RU DAYS -RV 1 -B c:\backups -BU DAYS -BV 1

4) Check the integrity of the AdventureWorks database and report to c:\reports keeping reports for 1 week expressmaint -S (local) -D AdventureWorks -T CHECKDB -R c:\reports -RU WEEKS -RV 1

5) Rebuild all indexes in the AdventureWorks database and report to c:\reports keeping reports for 1 day expressmaint -S (local) -D AdventureWorks -T REINDEX -R c:\reports -RU DAYS -RV 1

III. Setup Periodic Task Procedure 1) Extract “ExpressMaint.exe” from the zip file to “c:\ExpressMaint\” 2) Create batch file with desired command line parameters. In this example, report files are being sent to c:\reports. Backup files are being sent to c:\backups.

-2-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

Directory locations may be changed. ExpressMaint switches/arguments must be changed in the batch file to reflect changes in directory locations. The SQL server instance name is “XPSP3O2K7SP1\SQLEXPRESS” in this example. 3) After the batch file is verified to be working, the Windows Task Scheduler is needed in order to schedule the batch file to be run at regular intervals.

For Windows XP: a. Double-click Add Scheduled Task to start the Scheduled Task Wizard, and then click Next in the first dialog box

-3-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

b. Click Browse, browse to the location of the batch file, and then click Open.

-4-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

c. Type a name for the task e.g DAILY FULL BACKUP and then choose Daily from the scheduling options. Click Next.

-5-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

d. Specify the information about the time to run the task e.g. 11:00 PM, and then click Next

e. Type the name and password of the account that will execute this task. Make sure that you choose an account that is a SA for your instance, then click Next

-6-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

f.

Click Finish. New scheduled task will appear.

-7-

M:\PTDev\PT32\User Documents\Scheduling Backups for SQL Server 2005 Express.doc

-8-