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. %Database% E.g. %Database%, -Database1 |
| Directory | Backup root directory. | E.g. C:\Backup |
| BackupType | Type of backup. | FULL DIFF LOG |
| Verify | Is the backup to be verified? | Y N (DEFAULT) |
| CleanupTime | Time in hours after which the backup files are deleted. If not specified no backup files are deleted. | NULL (DEFAULT) E.g. 24 |
| Compress | Is the backup to be compressed? | Y * N (DEFAULT) |
| CopyOnly | Perform a copy-only backup. | Y N (DEFAULT) |
| ChangeBackupType | Change backup type if a differential or transaction log backup cannot be performed. | Y N (DEFAULT) |
| BackupSoftware | Third party backup software. If not specified sql server native backup is performed. | NULL (DEFAULT) LITESPEED |
| Execute | Execution of commands. Default the commands are executed normally. If set to N the commands are only printed. | Y (DEFAULT) N |
* Backup compression is only supported in SQL Server 2008 Enterprise and Developer Edition.
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. %Database% E.g. %Database%, -Database1 |
| PhysicalOnly | Limit the checks to the physical structures of the database. | Y N (DEFAULT) |
| NoIndex | Nonclustered indexes are not checked. | Y N (DEFAULT) |
| Execute | Execution of commands. Default the commands are executed normally. If set to N the commands are only printed. | Y (DEFAULT) N |
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. %Database% E.g. %Database%, -Database1 |
| 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) |
| SortInTempdb | Use tempdb for sort operations when rebuilding indexes. | Y N (DEFAULT) |
| MaxDOP | The number of CPUs that are used when rebuilding indexes. If not specified the global max degree of parallelism is used. | NULL (DEFAULT) 0 - 64 |
| FillFactor | Percentage that indicates how full the pages should be made when rebuilding indexes. If not specified the fillfactor in sys.indexes is used. | NULL (DEFAULT) 1 - 100 |
| LOBCompaction | Should pages that contain LOB columns be compacted when reorganizing indexes? | Y (DEFAULT) N |
| StatisticsSample | Percentage that indicates how much of a table that is gathered when updating statistics. If not specified SQL Server automatically computes the required sample. | NULL (DEFAULT) 1 - 100 |
| PartitionLevel | Optimize partitioned indexes on the partition level. | Y * N (DEFAULT) |
| Execute | Execution of commands. Default the commands are executed normally. If set to N the commands are only printed. | Y (DEFAULT) N |
* Online rebuild is only supported in Enterprise and Developer Edition.
* Table partitioning is only supported in Enterprise and Developer Edition.
Setup
To use the solution for backup you need the objects DatabaseBackup, CommandExecute and DatabaseSelect,
to use it for integrity check you need DatabaseIntegrityCheck, CommandExecute and DatabaseSelect
and to use it for index optimization you need IndexOptimize, CommandExecute and DatabaseSelect.
Execute the stored procedures from SQL Server Agent Jobs, through sqlcmd.
E.g. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SYSTEM_DATABASES'" -b
Use SQL Server Agent Job Output files with tokens for the logging.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT_JOB
_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt
The solution is supported on SQL Server 2005 and SQL Server 2008.
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 cannot be changed. |
Database Backup