I have developed a solution for backup, integrity check and index optimization in SQL Server 2005 and SQL Server 2008. The solution is based on stored procedures, functions, sqlcmd and SQL Server Agent jobs.

  • Dynamic selection of databases. Select all system databases, all user databases, all databases, a list of databases, exclude databases or do wildcard selections.
  • Database state check. If a database is not online the procedure logs the database state and continues to the next database. This feature has been designed for database mirroring, where the databases on the mirror server are in a restoring state.
  • Robust error handling and logging. If an error occurs the procedure logs the error and continues to the next database or index. In the end the job reports failure. Information about all commands are logged with start time, command text, command output, result, duration and end time.
  • Database backup features. Full, differential and transaction log backups. Automatic creation of backup directories. Backup file names with the name of the instance, the name of the database, backup type, date and time. Verification of backup files. Cleanup of old backup files. Support for compressed backups, LiteSpeed, COPY_ONLY backups and checksums. Option to dynamically change backup type if a differential or transaction log backup cannot be performed.

    EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24
  • Database integrity check using DBCC CHECKDB. Support for the options PHYSICAL_ONLY, NOINDEX and EXTENDED_LOGICAL_CHECKS.

    EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES'
  • Dynamic index optimization. Rebuild indexes online or offline, reorganize indexes, update statistics, reorganize indexes and update statistics or do nothing based on fragmentation level (checked with sys.dm_db_index_physical_stats) and the existence of columns with LOB data types. Support for partition level rebuilds and reorganizations.

    EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
    @FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
    @FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
    @FragmentationLow_LOB = 'NOTHING', @FragmentationLow_NonLOB = 'NOTHING',
    @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000
  • Download and install the solution in one script.
  • I have compared this solution for backup, integrity check and index optimization with the SQL Server 2005 and 2008 Maintenance Plans.
  • The solution has been through very thorough tests. It is being used in mission-critical environments in many organizations.
  • Stored procedures and functions in the solution.
    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.
  • Interested in getting information about updates to the solution? Please sign up for my newsmail.
  • The solution is free to use and modify.

Please feel free to contact me if you have any questions.