Database Backup

Procedure name: DatabaseBackup

Purpose: Create backup directories, backup databases, verify backups and delete old backups.

Parameters: See table below.

Name Description Supported Values
Databases Selection of databases. E.g. SYSTEM_DATABASES
E.g. USER_DATABASES
E.g. Database1
E.g. Database1, Database2
E.g. USER_DATABASES, master
E.g. SYSTEM_DATABASES, -master
Directory Backup root directory. E.g. C:\Backup
BackupType Type of backup. FULL
DIFF
LOG
Verify Is the backup to be verified? Y
N
CleanupTime Time in hours after which the backup files are deleted. E.g. 24


Integrity Check

Procedure name: DatabaseIntegrityCheck

Purpose: Check the integrity of databases.

Parameters: See table below.

Name Description Supported Values
Databases Selection of databases. E.g. SYSTEM_DATABASES
E.g. USER_DATABASES
E.g. Database1
E.g. Database1, Database2
E.g. USER_DATABASES, master
E.g. SYSTEM_DATABASES, -master


Index Optimization

Procedure name: IndexOptimize

Purpose: Rebuild and reorganize indexes and update statistics.

Parameters: See table below.

Name Description Supported Values
Databases Selection of databases. E.g. SYSTEM_DATABASES
E.g. USER_DATABASES
E.g. Database1
E.g. Database1, Database2
E.g. USER_DATABASES, master
E.g. SYSTEM_DATABASES, -master
FragmentationHigh_LOB Action to be performed on a high fragmented index that does contain a LOB. INDEX_REBUILD_OFFLINE (DEFAULT)
INDEX_REORGANIZE
STATISTICS_UPDATE
INDEX_REORGANIZE_STATISTICS_UPDATE
NOTHING
FragmentationHigh_NonLOB Action to be performed on a high fragmented index that does not contain a LOB. INDEX_REBUILD_ONLINE *
INDEX_REBUILD_OFFLINE (DEFAULT)
INDEX_REORGANIZE
STATISTICS_UPDATE
INDEX_REORGANIZE_STATISTICS_UPDATE
NOTHING
FragmentationMedium_LOB Action to be performed on a medium fragmented index that does contain a LOB. INDEX_REBUILD_OFFLINE
INDEX_REORGANIZE  (DEFAULT)
STATISTICS_UPDATE
INDEX_REORGANIZE_STATISTICS_UPDATE
NOTHING
FragmentationMedium_NonLOB Action to be performed on a medium fragmented index that does not contain a LOB. INDEX_REBUILD_ONLINE *
INDEX_REBUILD_OFFLINE
INDEX_REORGANIZE (DEFAULT)
STATISTICS_UPDATE
INDEX_REORGANIZE_STATISTICS_UPDATE
NOTHING
FragmentationLow_LOB Action to be performed on a low fragmented index that does contain a LOB. INDEX_REBUILD_OFFLINE
INDEX_REORGANIZE
STATISTICS_UPDATE
INDEX_REORGANIZE_STATISTICS_UPDATE
NOTHING (DEFAULT)
FragmentationLow_NonLOB Action to be performed on a low fragmented index that does not contain a LOB. INDEX_REBUILD_ONLINE *
INDEX_REBUILD_OFFLINE
INDEX_REORGANIZE
STATISTICS_UPDATE
INDEX_REORGANIZE_STATISTICS_UPDATE
NOTHING (DEFAULT)
FragmentationLevel1 The lower limit in percent for medium fragmentation. E.g. 5 (DEFAULT)
FragmentationLevel2 The lower limit in percent for high fragmentation. E.g. 30 (DEFAULT)
PageCountLevel Indexes under this size in pages are considered to be low fragmented regardless of actual fragmentation level. E.g. 1000 (DEFAULT)

* INDEX_REBUILD_ONLINE is only supported on Enterprise and Developer edition.

Setup

The stored procedures and functions are created in the master database or in a custom database for database administration.

DatabaseBackup - Stored procedure to backup databases.
DatabaseIntegrityCheck - Stored procedure to check integrity of databases.
IndexOptimize - Stored procedure to rebuild and reorganize indexes and update statistics.
CommandExecute - Stored procedure to execute and log commands.
DatabaseSelect - Function to select which databases to work with.

The stored procedures are executed from SQL Server Agent Jobs through sqlcmd.

E.g. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d db -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SYSTEM_DATABASES'" -b

Logging is performed using an SQL Server Agent Job Output file with tokens.

$(ESCAPE_SQUOTE(SQLDIR))\LOG\SQLAGENT_JOB_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

E.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT_JOB_0x0B837359DE5EC44881BAA6FB006F4DBC_1_20070617_150444.txt

This solution is supported on Microsoft SQL Server 2005 SP2.

Download and install the solution in one script.

Error Scenarios

Description Applies to Behavior
A database command fails. DatabaseBackup DatabaseIntegrityCheck The procedure logs the error and continues to the next database. In the end the job reports failure.
An index command fails. IndexOptimize The procedure logs the error and continues to the next index. In the end the job reports failure.
A backup command fails. DatabaseBackup The procedure skips the verify backup and delete backup steps for that database. The procedure then continues to the next database. In the end the job reports failure.
A verify backup command fails. DatabaseBackup The procedure skips the delete backup step for that database. The procedure continues to the next database. In the end the job reports failure.
A table is locked and the index command therefore cannot be performed. IndexOptimize When the index command has been waiting on locks for one hour it logs an error and the procedure continues to the next index. In the end the job reports failure.
A database is not in an Online state. DatabaseBackup DatabaseIntegrityCheck IndexOptimize The procedure logs that the database is not Online and continues to the next database. In the end the job reports success.
A table or index is dropped or altered after the job has started. IndexOptimize A final check is performed just before starting an index command. If the index has been dropped or altered the procedure skips the index and continues to the next index. In the end the job reports success.
A backup file is locked and cannot be deleted. DatabaseBackup The file is skipped with no waiting. In the end the job reports success. This is the behavior of the extended stored procedure xp_delete_file and can not be changed.