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. ALL_DATABASES E.g. Database1 E.g. Database1, Database2 E.g. %Database% E.g. %Database%, -Database1
Directory	Backup root directory.	NULL (DEFAULT) E.g. C:\Backup
BackupType	Type of backup.	FULL DIFF LOG
Verify	Verify the backup.	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	Compress the backup.	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 a sql server native backup is performed.	NULL (DEFAULT) LITESPEED SQLBACKUP
CheckSum	Enable backup checksums.	Y N (DEFAULT)
BlockSize	Physical blocksize in bytes.	NULL (DEFAULT)
BufferCount	The number of I/O buffers to be used for the backup operation.	NULL (DEFAULT)
MaxTransferSize	The largest unit of transfer in bytes to be used between SQL Server and the backup media.	NULL (DEFAULT)
NumberOfFiles	The number of backup files.	1 (DEFAULT)
CompressionLevel	LiteSpeed and SQLBackup compression level.	NULL (DEFAULT) 0 - 10 (LITESPEED) 0 - 4 (SQLBACKUP)
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.

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. ALL_DATABASES E.g. Database1 E.g. Database1, Database2 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)
ExtendedLogicalChecks	Perform extended logical checks.	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

* Extended logical checks is only supported in SQL Server 2008.

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. ALL_DATABASES E.g. Database1 E.g. Database1, Database2 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	Compact pages that contain LOB columns when reorganizing indexes.	Y (DEFAULT) N
StatisticsSample	Percentage that indicates how much of a table that is gathered when updating statistics. 100 is equivalent to a full scan. 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)
TimeLimit	Time in seconds after which no commands are executed.	NULL (DEFAULT) E.g. 3600
Execute	Execution of commands. Default the commands are executed normally. If set to 'N' the commands are only printed.	Y (DEFAULT) N

IndexOptimize places indexes into one of six groups based on their level of fragmentation (High, Medium or Low) and whether there are LOB columns (LOB or NonLOB).

It uses avg_fragmentation_in_ percent in sys.dm_db_index_physical_ stats to check the percentage of fragmentation in each index. Using the threshold limits you set in the @FragmentationLevel1 (lower threshold) and @FragmentationLevel2 (upper threshold) parameters, it places each index in the appropriate group. Indexes with fragmentation levels higher than the upper threshold go into one of the high fragmentation groups. Indexes with fragmentation levels between the two thresholds go into one of the medium fragmentation groups. Indexes with fragmentation levels under the lower threshold go into one of the low fragmentation groups.

For each group, you can select one of the following actions:

Action	Description
INDEX_REBUILD_ONLINE *	Rebuild indexes online.
INDEX_REBUILD_OFFLINE	Rebuild indexes offline.
INDEX_REORGANIZE	Reorganize indexes.
INDEX_REORGANIZE_STATISTICS_UPDATE	Reorganize indexes and update statistics.
STATISTICS_UPDATE	Update statistics.
NOTHING	Do nothing.

* Online rebuild is only supported in Enterprise and Developer Edition.

* Table partitioning is only supported in Enterprise and Developer Edition.

DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize all have a parameter called @Databases that are used for selecting which databases to work with. It can be used like this.

Usage	Description
@Databases = 'SYSTEM_DATABASES'	All system databases (master, msdb and model).
@Databases = 'USER_DATABASES'	All user databases.
@Databases = 'ALL_DATABASES'	All databases.
@Databases = 'Database1'	The database Database1.
@Databases = 'Database1, Database2'	The databases Database1 and Database2.
@Databases = 'USER_DATABASES, -Database1'	All user databases, except Database1.
@Databases = '%Database%'	All databases that have Database in the name.
@Databases = '%Database%, -Database1'	All databases that have Database in the name, except Database1.
@Databases = 'ALL_DATABASES, -%Database%'	All databases that do not have Database in the name.

The keywords SYSTEM_DATABASES, USER_DATABASES and ALL_DATABASES are supported. The (-) character is used to exclude databases and the (%) character is used for wildcard selection. All of these things can be combined using the (,) character.

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 and the -b option.

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.

E.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\DatabaseBackup
_$(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.

The maintenance solution is free.

You are allowed to use it both commercially and non-commercially. You are allowed to modify the solution.

Here's an easy way to get started with the solution.

1. Download MaintenanceSolution.sql.

2. Change this line in the script to your backup directory.
SET @BackupDirectory = N'C:\Backup' -- <== Change this to your backup directory.

3. Execute MaintenanceSolution.sql.
This script creates the stored procedures, the function and the jobs that you need.

4. Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that they're completing successfully.
Verify that the backup files are created. Check the output files in the error log directory.

5. Schedule the jobs.

Description	Behavior
A backup command fails.	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.	The procedure skips the delete backup step for that database. The procedure continues to the next database. In the end the job reports failure.
An integrity check command fails.	The procedure logs the error and continues to the next database. In the end the job reports failure.
An index rebuild or index reorganize command fails.	The procedure logs the error and continues to the next index. In the end the job reports failure.
A table is locked and the index rebuild or index reorganize is waiting.	When the index rebuild or index reorganize 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.

Question	Answer
I'm getting this error message when I try to create the objects. "Msg 102, Level 15, State 1, Procedure IndexOptimize, Line 456" "Incorrect syntax near '('."	This error is because of that the database that you are creating the objects in, is not in compatibility level 90 or 100. Change the compatibility level to 90 or 100 and try again. EXECUTE dbo.sp_dbcmptlevel @dbname=N'DatabaseName', @new_cmptlevel=90
Does all databases have to be in compatibility level 90 or 100?	It is only the database that you are creating the objects in, that has to be in compatibility level 90 or 100. All other databases can be in any compatibility level.
I'm getting these informational messages when I create the stored procedure DatabaseBackup, DatabaseIntegrityCheck or IndexOptimize. "Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.CommandExecute'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists." "The module 'DatabaseBackup' depends on the missing object 'dbo.CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists."	DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize are using the stored procedure CommandExecute and the function DatabaseSelect. Download these objects and create them in the same database. You could also use the script MaintenanceSolution.sql. This script contains all the objects that you need.
I'm getting these error messages, when I execute the stored procedure DatabaseBackup, DatabaseIntegrityCheck or IndexOptimize. "Msg 208, Level 16, State 1, Procedure DatabaseBackup, Line 111" "Invalid object name 'dbo.DatabaseSelect'." "Msg 2812, Level 16, State 62, Procedure DatabaseBackup, Line 397" "Could not find stored procedure 'dbo.CommandExecute'."	DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize are using the stored procedure CommandExecute and the function DatabaseSelect. Download these objects and create them in the same database. You could also use the script MaintenanceSolution.sql. This script contains all the objects that you need.
Does the stored procedures have to be executed in a CmdExec job step with sqlcmd? Can I not use a T-SQL job step?	The stored procedures have to be executed in a CmdExec job step with sqlcmd and the -b option. Otherwise the error handling and logging will not work as designed. You could use the script MaintenanceSolution.sql to create the jobs.
My job stops after the first error. What could the reason be for that?	This is the behaviour if you're using T-SQL job steps. Then the job stops at the first error. I recommend that you use CmdExec job steps with sqlcmd and the -b option. Then the job will continue after an error. In the end the job will report failure. You could use the script MaintenanceSolution.sql to create the jobs.
The output files have a [SQLSTATE 01000] or [SQLSTATE 42000] in the end of each line. Why does it look like this?	This is the behaviour if you're using T-SQL job steps. I recommend that you use CmdExec job steps with sqlcmd and the -b option. Then this will go away. You could use the script MaintenanceSolution.sql to create the jobs.
Can IndexOptimize be used to update statistics?	Yes, IndexOptimize can be used to update statistics. If you use these parameters it will reorganize indexes and update the statistics, instead of only reorganize the indexes. @FragmentationMedium_LOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE' For high fragmentation the indexes are rebuilt. Then the statistics are automatically updated.
I'm getting this error message when I'm executing IndexOptimize. Why do I get this error? "Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 103" "Msg 1222, Lock request time out period exceeded."	This is because of that one of your indexes is locked by another connection. IndexOptimize can therefore not rebuild or reorganize the index.
Can the solution be used with SQL Server Express?	The solution is supported on SQL Server Express. SQL Server Express has no SQL Server Agent. Therefore the execution of the stored procedures has to be scheduled using cmd - files and Windows Scheduled Tasks instead.
I'm using an old version of the solution. How do I upgrade to the latest version?	The easiest way to upgrade to the latest version is to use the script MaintenanceSolution.sql. This script will drop and recreate all the objects in the solution. It will not drop or change any jobs. All your existing jobs will continue to work unchanged as all the objects are backward compatible.
What should the fragmentation levels be for reorganization and rebuild of indexes?	Microsoft has some recommendations in Books Online. The default values in IndexOptimize are based on this.
Some of my indexes are still fragmented after I have just executed IndexOptimize. How could that be?	Very small indexes sometimes shows a high fragmentation even if they've just been rebuilt or reorganized. In addition fragmentation on very small tables has no impact on performance. Microsoft has a whitepaper about this. "Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
What do I have to think about to backup to a network share?	The SQL Server service account needs to have full control of the directory and network share that you're backing up to. This is easiest achieved by running SQL Server under a domain account and granting the permissions to that account.
DatabaseBackup is not deleting old backup files. What could the problem be?	It could be because of one of the following things. 1. Windows security. Check that the SQL Server service account has full control of the backup directory. If it's a network share, also check that the SQL Server service account has full control of the network share. 2. File system locking. It could for example be a backup or anti-virus software that is locking the file. 3. DatabaseBackup has been designed not to delete transaction log backups that are newer than the latest full or differential backup.
I'm getting this error message when I'm backing up a database with DatabaseBackup. Doesn't DatabaseBackup check if a backup can be done? "Msg 3035, Level 16, State 10, Line 3" "Cannot perform a differential backup for database "Database", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option."	DatabaseBackup is checking sys.master_files.differential_base_lsn to see if a differential backup can be done. If it cannot be done it is doing one of two things. Default the database is skipped. A full backup can also be performed. This is if @ChangeBackupType = 'Y'. Unfortunately this does not work if a database from a SQL Server 2000 server is restored or attached. Then you get this error message
How can I tune performance of backup compression in SQL Server 2008?	The Microsoft SQLCAT - team has an article about how to tune performance of backup compression in SQL Server 2008. You can tune performance by using multiple backup files and by using the options BUFFERCOUNT and MAXTRANSFERSIZE.
I'm executing MaintenanceSolution.sql. The stored procedures and the function are created, but not the jobs. How could that be?	This could be because of that you are using SQL Server Express. SQL Server Express does not have SQL Server Agent. Therefore the jobs are not created. You can use cmd - files and Windows Scheduled Tasks instead.
I'm getting the following error message when I'm executing the jobs. "Unable to start execution of step 1 (reason: The job step contains tokens. For SQL Server 2005 Service Pack 1 or later, you must use the appropriate ESCAPE_xxx macro to update job steps containing tokens before the job can run.)"	The syntax for SQL Server Agent tokens has changed between SQL Server 2005 RTM and SQL Server 2005 SP1. If you are upgrading from SQL Server 2005 RTM to a later service pack or version you need to update the jobs. Microsoft has a script that can help you with this.
I have read something about Service Pack requirements on SQL Server 2005. What Service Pack is required?	The most recent version of the solution is supported on all versions and editions of SQL Server 2005 and SQL Server 2008. No Service Pack is required.
Is the solution using xp_cmdshell?	The solution is not using xp_cmdshell. It works on an out of the box SQL Server 2005 or SQL Server 2008 installation.
Is the solution supported on SQL Server 2000?	The solution is only supported and only works on SQL Server 2005 and SQL Server 2008. I'm using a lot of things that were introduced in SQL Server 2005; catalog views, dynamic management views, TRY CATCH, ALTER INDEX and some more.
Isn't it easier just to use the SQL Server Maintenance Plans?	The SQL Server Maintenance Plans are good in some situations, but it's not always what you need. This solution is very reliable, easily deployable, has extensive logging and has the features that often are needed in an enterprise environment.
Is the solution free?	The solution is free and will stay free. You can use it in your database administration, in your hosting environment, in your consultancy work or distribute it with your software. There are no limitations.
I'm a so called accidental DBA. How do I get started with your solution?	It's easy to get started with my solution. I have made a Getting Started Guide that I recommend you to start with.

There are some SQL Server features that would be very useful in this solution if Microsoft would add them.
Vote for them on Microsoft Connect.

• Rebuild an index partition online - 252433
  

• Statistics on the partition level - 328093
  

• DMV to check the percentage of a database that has changed since the last full backup - 511305
  

• MAXDOP option in DBCC CHECKDB - 468694
  

• CHECKCONSTRAINTS option in DBCC CHECKDB - 508837
  

• Continue SQL Server Agent T-SQL job step execution after an error - 244855
  

• Include output files in SQL Server Agent Job failure notification - 293041
  

• SQL Server 2008 R2 sqlcmd bug, output truncated after 512 characters - 510344