I have made 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, e.g. USER_DATABASES.
  • 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 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 backups using RESTORE VERIFYONLY. Deletion of old backup files.

    EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24
  • Database integrity check using DBCC CHECKDB.

    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 LOB existence.

    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.
  • How does this solution for backup, integrity check and index optimization do against the SQL Server 2005 Maintenance Plans? I have compared the two solutions.
  • 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.
  • The solution is free to use and modify.

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