Set Up the Backup routine using the Windows Schedule Task - SQL Express

12/05/2017 16:48

1º - Prepare the database objects on Master DB

-------------------------------------------------------------------

 

USE [master]

GO

/****** Object:  StoredProcedure [DBO].[spMaintenance]    Script Date: 13/02/2017 11:16:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

if OBJECT_ID('[DBO].[spMaintenance]') > 0

                drop proc [DBO].[spMaintenance]

go          

Create proc [DBO].[spMaintenance]

 

@Operation varchar(50)               -- BackupDB, BackupLog, CycleSQLLog, ReIndex

 

as

/******************************************************************************

 

Name:  DBO.spMaintenance

 

Run manual maintenance tasks. Uusally needed for SQL Express Edition bacause of

the lack of the SQL Agent. Must be called externally from a scheduler using SQLCMD.

 

******************************************************************************

Change History

******************************************************************************

Date:                                     Author:                                                                                Description:

-----                                        -------                                                                                     -------------------

13 Jan 2017                         Leandro Buffone - Turners           Created

*****************************************************************************

 

This is run from a Windows Task Scheduler with the following config

 

User:                     SYSTEM (or a user with a non expiring password)

Program:             "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"

Arguments:        -Usa -PPW -Q "exec DBO.spMaintenance @Operation='ReIndex'"

 

Usage:

 

exec DBO.spMaintenance @Operation = 'BackupDB'

exec DBO.spMaintenance @Operation = 'BackupLog'

exec DBO.spMaintenance @Operation = 'ReIndex'

exec DBO.spMaintenance @Operation = 'CycleLog'

 

*/

if @Operation = 'BackupDB'

                exec DBO.spExpressMaint

                   @Database      = 'ALL_SYSTEM',

                   @OpType        = 'DB',

                   @BackupFldr    = 'D:\SQL\Backup',

                   @ReportFldr    = 'D:\SQL\Maintenance',

                   @Verify        = 1,

                   @DBRetainUnit  = 'days',

                   @DBRetainVal   = 4,

                   @RptRetainUnit = 'weeks',

                   @RptRetainVal  = 1,

                   @Report        = 1

 

if @Operation = 'BackupDB'

                exec DBO.spExpressMaint

                   @Database      = 'ALL_USER',

                   @OpType        = 'DB',

                   @BackupFldr    = 'D:\SQL\Backup',

                   @ReportFldr    = 'D:\SQL\Maintenance',

                   @Verify        = 1,

                   @DBRetainUnit  = 'days',

                   @DBRetainVal   = 4,

                   @RptRetainUnit = 'weeks',

                   @RptRetainVal  = 1,

                   @Report        = 1

 

if @Operation = 'BackupLog'

                exec DBO.spExpressMaint

                   @Database      = 'ALL_USER',

                   @OpType        = 'LOG',

                   @BackupFldr    = 'D:\SQL\Backup',

                   @ReportFldr    = 'D:\SQL\Maintenance',

                   @Verify        = 1,

                   @DBRetainUnit  = 'days',

                   @DBRetainVal   = 4,

                   @RptRetainUnit = 'weeks',

                   @RptRetainVal  = 1,

                   @Report        = 1

 

if @Operation = 'ReIndex'

                exec DBO.spIndexOptimize @Databases = 'ALL_DATABASES'

 

if @Operation = 'CycleLog'

                exec sp_cycle_errorlog

 

Go

 

USE [master]

GO

/****** Object:  StoredProcedure [DBO].[spExpressMaint]    Script Date: 13/02/2017 11:26:25 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

if OBJECT_ID('[DBO].[spExpressMaint]') > 0

                drop proc [DBO].[spExpressMaint]

go

 

CREATE proc [DBO].[spExpressMaint]

 

@database      sysname,                   -- database name | ALL_USER | ALL_SYSTEM

@optype        varchar(7),                -- LOG | DB | DIFF | REINDEX | REORG | CHECKDB

@backupwith    varchar(500) = NULL,       -- additional backup options

@backupfldr    varchar(200) = NULL,       -- folder to write backup to

@reportfldr    varchar(200) = NULL,       -- folder to write text report

@verify        bit = 1,                   -- verify backup

@verifywith    varchar(500) = NULL,       -- additional verify options

@dbretainunit  varchar(10)  = NULL,       -- minutes | hours | days | weeks | months | copies

@dbretainval   int = 1,                   -- specifies how many retainunits to keep backup

@report        bit = 1,                   -- flag to indicate whether to generate report

@rptretainunit varchar(10)  = NULL,       -- minutes | hours | days | weeks | months | copies

@rptretainval  int = 1,                   -- specifies how many retainunits to keep reports

@checkattrib   bit = 0,                   -- check if archive bit is cleared before deleting

@delfirst      bit = 0,                   -- delete before backup (handy if space issues)

@debug         bit = 0                    -- print commands to be executed

 

as

/******************************************************************************

 

Name:  DBO.spExpressMaint

 

see https://www.sqlDBOtips.com/showarticle.asp?ID=27 for documentation

  

Date           Author                  Notes

24/07/2004     Jasper Smith            Initial release

 

Usage:

 

exec DBO.spExpressMaint

                @Database      = 'ALL_USER',

                @OpType        = 'LOG',

                @BackupFldr    = 'D:\SQL\Backup',

                @ReportFldr    = 'D:\SQL\Maintenance',

                @Verify        = 1,

                @DBRetainUnit  = 'days',

                @DBRetainVal   = 4,

                @RptRetainUnit = 'weeks',

                @RptRetainVal  = 1,

                @Report        = 1

 

*/

SET NOCOUNT ON

SET DATEFORMAT YMD

 

/************************

   VARIABLE DECLARATION

************************/

 

   DECLARE @fso             int

   DECLARE @file            int

   DECLARE @reportfilename  varchar(500)

   DECLARE @backupfilename  varchar(500)

   DECLARE @delfilename     varchar(500)

   DECLARE @cmd             varchar(650)

   DECLARE @backupfldrorig  varchar(200)

   DECLARE @databaseorig    sysname

   DECLARE @table           nvarchar(600)

   DECLARE @exists          varchar(5)

   DECLARE @err             int

   DECLARE @start           datetime

   DECLARE @finish          datetime

   DECLARE @runtime         datetime

   DECLARE @output          varchar(200)

   DECLARE @errormsg        varchar(210)

   DECLARE @datepart        nchar(2)

   DECLARE @execmd          nvarchar(1000)

   DECLARE @delcmd          nvarchar(1000)

   DECLARE @exemsg          varchar(8000)

   DECLARE @filecount       int              ; SET @filecount    = 0

   DECLARE @delcount        int              ; SET @delcount     = 0

   DECLARE @hr              int              ; SET @hr           = 0

   DECLARE @ret             int              ; SET @ret          = 0

   DECLARE @cmdret          int              ; SET @cmdret       = 0

   DECLARE @delbkflag       int              ; SET @delbkflag    = 0

   DECLARE @delrptflag      int              ; SET @delrptflag   = 0

   DECLARE @filecrt         int              ; SET @filecrt      = 0

   DECLARE @user            sysname          ; SET @user         = SUSER_SNAME()

   DECLARE @jobdt           datetime         ; SET @jobdt        = GETDATE()

   DECLARE @jobstart        char(12)         ;

   DECLARE @stage           int              ; SET @stage        = 1

 

   SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),':',''),4)  

   IF RIGHT(@reportfldr,1)<>'\' SET @reportfldr = @reportfldr + '\'

   IF RIGHT(@backupfldr,1)<>'\' SET @backupfldr = @backupfldr + '\'

   SET @backupfldrorig = @backupfldr

   SET @databaseorig = @database

 

   CREATE TABLE #files(filename varchar(255)) 

   CREATE TABLE #exists(exist int,isdir int,parent int)

   CREATE TABLE #databases(dbname sysname)

 

/**********************************

     INITIALIZE FSO IF @report = 1

***********************************/

 

   IF @report = 1

   BEGIN

      EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

      IF @hr <> 0

      BEGIN  

         EXEC sp_OAGetErrorInfo @fso

         RAISERROR('Error creating File System Object',16,1)

         SET @ret = 1

               GOTO CLEANUP

      END

   END

 

/************************

       CHECK INPUT

************************/

 

   -- check sysadmin

   IF IS_SRVROLEMEMBER('sysadmin') = 0

                BEGIN                                                                                

                RAISERROR('The current user %s is not a member of the sysadmin role',16,1,@user)

      SET @ret = 1

                GOTO CLEANUP

                END

 

   -- check database exists and is online

   IF @database NOT IN ('ALL_USER','ALL_SYSTEM')

   BEGIN

      IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)

                BEGIN                                                                                

               RAISERROR('Database %s is invalid or database status is not ONLINE',16,1,@database)

         SET @ret = 1

               GOTO CLEANUP               

                END

   END

 

   -- check @optype is valid

   IF UPPER(@optype) NOT IN ('LOG','DB','DIFF','REINDEX','REORG','CHECKDB')

                BEGIN                                                                                

                RAISERROR('%s is not a valid option for @optype',16,1,@optype)

      SET @ret = 1

                GOTO CLEANUP               

                END

 

   -- check recovery mode is correct if trying log backup

   IF @database NOT IN ('ALL_USER','ALL_SYSTEM')

   BEGIN

      IF (@optype = 'LOG' and ((select recovery_model from sys.databases where name = @database) = 3))

                BEGIN                                                                                

               RAISERROR('%s is not a valid option for database %s because it is in SIMPLE recovery mode',16,1,@optype,@database)

         SET @ret = 1

               GOTO CLEANUP

                END

   END

 

   -- no log backups for system databases

   IF @database = 'ALL_SYSTEM'

   BEGIN

      IF @optype = 'LOG'

                BEGIN                                                                                

               RAISERROR('%s is not a valid option for the option ALL_SYSTEM',16,1,@optype)

         SET @ret = 1

               GOTO CLEANUP

                END

   END

 

   -- check that @backupfldr exists on the server

   IF @optype NOT IN ('REINDEX','CHECKDB','REORG')

   BEGIN

      IF @report = 1

      BEGIN

         EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@backupfldr

         IF @exists <> 'True'

               BEGIN                                                                                

               RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)

            SET @ret = 1

               GOTO CLEANUP

               END

      END

      ELSE

      BEGIN

         INSERT #exists

         EXEC master.dbo.xp_fileexist @backupfldr

         IF (SELECT MAX(isdir) FROM #exists)<>1

               BEGIN                                                                                

               RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)

            SET @ret = 1

               GOTO CLEANUP

               END

      END

   END

 

   -- check that @reportfldr exists on the server

   IF @reportfldr IS NOT NULL or @report = 1

   BEGIN

      IF @report = 1

      BEGIN

         EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@reportfldr

         IF @exists <> 'True'

               BEGIN                                                                                

               RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)

            SET @ret = 1

               GOTO CLEANUP

               END

      END

      ELSE

      BEGIN

         DELETE #exists

         INSERT #exists

         EXEC master.dbo.xp_fileexist @reportfldr

         IF (SELECT MAX(isdir) FROM #exists)<>1

               BEGIN                                                                                

               RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)

            SET @ret = 1

               GOTO CLEANUP

               END

      END

   END

 

   -- check @dbretainunit is a vaild value

   IF @optype NOT IN ('REINDEX','CHECKDB','REORG')

   BEGIN

      IF UPPER(@dbretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES')

                BEGIN                                                                                

               RAISERROR('%s is not a valid value for @dbretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@dbretainunit)

         SET @ret = 1

               GOTO CLEANUP

                END

   END

 

   --check @dbretainval is a vaild value

   IF @dbretainval<1

                BEGIN                                                                                

                RAISERROR('%i is not a valid value for @dbretainval (must be >0)',16,1,@dbretainval)

      SET @ret = 1

                GOTO CLEANUP

                END

 

   -- check @rptretainunit is a vaild value if present

   IF UPPER(@rptretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES') and @rptretainunit IS NOT NULL

                BEGIN                                                                                

                RAISERROR('%s is not a valid value for @rptretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@rptretainunit)

      SET @ret = 1

                GOTO CLEANUP

                END

 

   --check @rptretainval is a vaild value

   IF @rptretainval<1

                BEGIN                                                                                

                RAISERROR('%i is not a valid value for @rptretainval (must be >0)',16,1,@rptretainval)

      SET @ret = 1

                GOTO CLEANUP

                END

 

/***********************************

   list of databases to process

************************************/

 

   IF @database IN ('ALL_USER','ALL_SYSTEM')

   BEGIN

      IF @database = 'ALL_USER'

         INSERT #databases(dbname)

         SELECT [name] from sys.databases where database_id > 4

         AND (@optype <> 'LOG' OR recovery_model <> '3')

      ELSE

         INSERT #databases(dbname)

         SELECT [name] from sys.databases where database_id in (1,3,4)

   END

   ELSE

      INSERT #databases(dbname) SELECT @database

 

 

/***********************************

   INITIALIZE REPORT IF @report = 1

************************************/

 

   -- generate report filename

   SELECT @reportfilename = @reportfldr + REPLACE(@database,' ','_') +

   CASE WHEN UPPER(@optype) = 'DB'   THEN '_FullDBBackup_report_'

        WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'

        WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_report_'  

        WHEN UPPER(@optype) = 'REINDEX'  THEN '_Reindex_report_'   

        WHEN UPPER(@optype) = 'REORG'  THEN '_Reorg_report_'  

        WHEN UPPER(@optype) = 'CHECKDB'  THEN '_CheckDB_report_'     

   END + @jobstart + '.txt'

 

   -- if no report just set @reportfilename to NULL

   IF @report = 0 SET @reportfilename = NULL

 

   IF @debug = 1

   BEGIN

      PRINT '@reportfilename = ' + ISNULL(@reportfilename,'NULL')

   END

 

   IF @report = 1

   BEGIN

      -- create report file

      EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @reportfilename

      IF (@hr <> 0)

      BEGIN

         EXEC sp_OAGetErrorInfo @fso

         RAISERROR('Error creating log file',16,1)

         SET @ret = 1

               GOTO CLEANUP

      END

      ELSE

         -- set global flag to indicate we have created a report file

         SET @filecrt = 1

 

      -- write header  

      EXEC sp_OAMethod @file,'WriteLine',NULL,''

      SET @output = 'Expressmaint utility, Logged on to SQL Server [' + @@SERVERNAME + '] as ' + '[' + @user + ']'

      IF @debug = 1 PRINT @output

      EXEC sp_OAMethod @file,'WriteLine',NULL,@output      

     

 

      IF UPPER(@optype) NOT IN ('REINDEX','CHECKDB','REORG')

      BEGIN

         SET @output = 'Starting backup on ' + convert(varchar(25),getdate(),100)

      END

      IF UPPER(@optype) = 'CHECKDB'

      BEGIN

         SET @output = 'Starting CheckDB on ' + convert(varchar(25),getdate(),100)

      END     

      IF UPPER(@optype) IN ('REINDEX','REORG')

      BEGIN

         SET @output = 'Starting Reindex on ' + convert(varchar(25),getdate(),100)

      END

 

      IF @debug = 1 PRINT @output

      EXEC sp_OAMethod @file,'WriteLine',NULL,@output

      EXEC sp_OAMethod @file,'WriteLine',NULL,''

   END

 

/************************

     BACKUP ACTIONS

************************/

 

   IF  UPPER(@optype) = 'CHECKDB' GOTO CHECK_DB

   IF  UPPER(@optype) IN ('REINDEX','REORG') GOTO REINDEX

  

   -- if @delfirst = 1  we need to delete prior backups that qualify

   IF @delfirst = 1 GOTO DELFIRST

 

   -- this label is so that we can return here after deleting files if @delfirst = 1

   DOBACKUP:

 

   DECLARE dcur CURSOR LOCAL FAST_FORWARD

   FOR SELECT dbname FROM #databases ORDER BY dbname

   OPEN dcur

   FETCH NEXT FROM dcur into @database

   WHILE @@FETCH_STATUS=0

   BEGIN

  

      -- set backup start time

      SET @start = GETDATE()

  

      -- write to text report

      IF @report = 1

      BEGIN

         SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': ' +

                       CASE WHEN UPPER(@optype) = 'DB'   THEN 'Full Backup '

                            WHEN UPPER(@optype) = 'DIFF' THEN 'Differential Backup '

                            WHEN UPPER(@optype) = 'LOG'  THEN 'Log Backup '        

                       END + 'starting at ' + CONVERT(varchar(25),@start,100)

         IF @debug = 1 PRINT @output

         EXEC sp_OAMethod @file,'WriteLine',NULL,@output

      END

 

      -- backup subfolder

      SET @execmd = 'IF NOT EXIST "' + @backupfldrorig + @database + '\" MKDIR "' + @backupfldrorig + @database + '\"'

      EXEC master.dbo.xp_cmdshell @execmd,no_output

      SET @backupfldr = @backupfldrorig + @database + '\'

 

      SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +

      CASE WHEN UPPER(@optype) = 'DB'   THEN '_FullDBBackup_'

           WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'

           WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_'        

      END + @jobstart +

      CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

        

      /************************

             FULL BACKUP

      ************************/

  

      IF UPPER(@optype) = 'DB'

      BEGIN

  

         SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +

                       CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END

  

         BEGIN TRY

  

            EXEC(@execmd)

  

         END TRY

         BEGIN CATCH -- backup failure

            SELECT @err = @@ERROR,@ret = @err

            SELECT @errormsg = 'Full backup of database ' + @database + ' failed with error : ' +  CAST(@err as varchar(10))

            SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'

            IF @debug = 1 PRINT @output

            IF @report = 1

            BEGIN

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               EXEC sp_OAMethod @file,'WriteLine',NULL,''

            END

            CLOSE dcur

            DEALLOCATE dcur

            GOTO CLEANUP

  

         END CATCH

        

         -- backup success

         SET @finish = GETDATE()

         SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

         END

  

         --calculate backup runtime

         SET @runtime = (@finish - @start)

         SET @output = SPACE(4) + 'Full database backup completed in '

                     + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '

                     + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '

                     + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

         END

  

      END

  

  

      /************************

         DIFFERENTIAL BACKUP

      ************************/

  

      IF UPPER(@optype) = 'DIFF'

      BEGIN

  

         SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' +

                        @backupfilename + ''' WITH DIFFERENTIAL' +

                       CASE WHEN @backupwith IS NULL THEN '' ELSE (' , ' + @backupwith) END

  

         BEGIN TRY

  

            EXEC(@execmd)

  

         END TRY

         BEGIN CATCH -- backup failure

  

            SELECT @err = @@ERROR,@ret = @err

            SELECT @errormsg = 'Differential backup of database ' + @database + ' failed with error : ' +  CAST(@err as varchar(10))

            SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'

            IF @debug = 1 PRINT @output

            IF @report = 1

            BEGIN

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            END

            CLOSE dcur

            DEALLOCATE dcur

            GOTO CLEANUP

  

         END CATCH

        

         -- backup success

         SET @finish = GETDATE()

         SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

         END

  

         --calculate backup runtime

         SET @runtime = (@finish - @start)

         SET @output = SPACE(4) + 'Differential database backup completed in '

                     + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '

                     + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '

                     + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

         END

  

      END

  

      /************************

             LOG BACKUP

      ************************/

     

      IF UPPER(@optype) = 'LOG'

      BEGIN

  

         SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +

                       CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END

  

         BEGIN TRY

  

            EXEC(@execmd)

  

         END TRY

         BEGIN CATCH -- backup failure

  

            SELECT @err = @@ERROR,@ret = @err

            SELECT @errormsg = 'Log backup of database ' + @database + ' failed with error : ' +  CAST(@err as varchar(10))       

            SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'

            IF @debug = 1 PRINT @output

            IF @report = 1

            BEGIN

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            END

            CLOSE dcur

            DEALLOCATE dcur

            GOTO CLEANUP

  

         END CATCH

        

         -- backup success

         SET @finish = GETDATE()

         SET @output = SPACE(4) + 'Log backed up to ' + @backupfilename

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

         END

  

         --calculate backup runtime

         SET @runtime = (@finish - @start)

         SET @output = SPACE(4) + 'Log backup completed in '

                     + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '

                     + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '

                     + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

         END

  

      END

 

      SET @stage = (@stage + 1)

 

      FETCH NEXT FROM dcur into @database

   END

 

   CLOSE dcur

   DEALLOCATE dcur

    

   /************************

         VERIFY BACKUP

   ************************/

 

   IF @verify = 1

   BEGIN

 

      DECLARE dcur CURSOR LOCAL FAST_FORWARD

      FOR SELECT dbname FROM #databases ORDER BY dbname

      OPEN dcur

      FETCH NEXT FROM dcur into @database

      WHILE @@FETCH_STATUS=0

      BEGIN

  

         SELECT @backupfilename = @backupfldrorig + @database + '\' + REPLACE(@database,' ','_') +

         CASE WHEN UPPER(@optype) = 'DB'   THEN '_FullDBBackup_'

              WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'

              WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_'        

         END + @jobstart +

         CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

     

         SET @start = GETDATE()

  

         -- write to text report

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

            SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Verify Backup File...'

            IF @debug = 1 PRINT @output

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output 

         END

        

         SET @execmd = N'RESTORE VERIFYONLY FROM DISK = ''' + @backupfilename + '''' +

                       CASE WHEN @verifywith IS NULL THEN '' ELSE (' WITH ' + @verifywith) END

        

         BEGIN TRY

  

            EXEC(@execmd)

  

         END TRY

         BEGIN CATCH

  

            SELECT @err = @@ERROR,@ret = @err

            SET @errormsg = 'Verify of ' + @backupfilename + ' failed with Native Error : ' + CAST(@err as varchar(10))

            SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'

            IF @debug = 1 PRINT @output

            IF @report = 1

            BEGIN

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            END

            CLOSE dcur

            DEALLOCATE dcur

            GOTO CLEANUP

  

         END CATCH

  

         -- verify success

         SET @finish = GETDATE()

         SET @output = SPACE(4) + 'Backup file ' + @backupfilename + ' verified'

         IF @debug = 1 PRINT @output

  

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

         END

  

         --calculate verify runtime

         SET @runtime = (@finish - @start)

         SET @output = SPACE(4) + 'Verify backup completed in '

                     + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '

                     + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '

                     + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

         END

 

         SET @stage = (@stage + 1)

         FETCH NEXT FROM dcur into @database

      END    

 

      CLOSE dcur

      DEALLOCATE dcur

   END

 

/************************

    DELETE OLD FILES

************************/

 

 

   -- we have already deleted files so skip to the end

   IF @delfirst = 1 GOTO CLEANUP

 

   -- this label is so that we can delete files prior to backup if @delfirst = 1

   DELFIRST:

 

   /************************

      DELETE OLD BACKUPS

   ************************/

 

   SET @datepart = CASE

      WHEN UPPER(@dbretainunit) = 'MINUTES' THEN N'mi'

      WHEN UPPER(@dbretainunit) = 'HOURS'   THEN N'hh'

      WHEN UPPER(@dbretainunit) = 'DAYS'    THEN N'dd'

      WHEN UPPER(@dbretainunit) = 'WEEKS'   THEN N'ww'

      WHEN UPPER(@dbretainunit) = 'MONTHS'  THEN N'yy'

   END

 

   IF @debug = 1 PRINT '@datepart for backups = ' + @datepart

 

   -- write to text report

   IF @report = 1

   BEGIN

      EXEC sp_OAMethod @file,'WriteLine',NULL,''

   END

   SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files...'

   IF @debug = 1 PRINT @output

   IF @report = 1

   BEGIN

      EXEC sp_OAMethod @file,'WriteLine',NULL,@output

   END

 

   DECLARE dcur CURSOR LOCAL FAST_FORWARD

   FOR SELECT dbname FROM #databases ORDER BY dbname

   OPEN dcur

   FETCH NEXT FROM dcur into @database

   WHILE @@FETCH_STATUS=0

   BEGIN

 

      SET @backupfldr = + @backupfldrorig + @database + '\'

      SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +

      CASE WHEN UPPER(@optype) = 'DB'   THEN '_FullDBBackup_'

           WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'

           WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_'        

      END + @jobstart +

      CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END

  

      -- load files in @backupfldr

      IF @checkattrib = 1

         SET @cmd = 'dir /B /A-D-A /OD "' + @backupfldr + REPLACE(@database,' ','_') +

         CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'

              WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'

              WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_' END + '*' +

         CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'

      ELSE

         SET @cmd = 'dir /B /A-D /OD "' + @backupfldr + REPLACE(@database,' ','_') +

         CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'

              WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'

              WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_' END + '*' +

         CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'

  

      IF @debug = 1 PRINT '@cmd = ' + @cmd

 

      DELETE #files

      INSERT #files EXEC master.dbo.xp_cmdshell @cmd

      DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,''),'nothing')

  

      IF @debug = 1 SELECT * FROM #files

     

      -- get count of files that match pattern

      SELECT @filecount = COUNT(*) from #files

      WHERE PATINDEX('%File Not Found%',filename) = 0

      AND PATINDEX('%The system cannot find%',filename) = 0

  

      -- remove files that don't meet retention criteria if there are any files that match pattern

      IF UPPER(@dbretainunit) <> 'COPIES'

      BEGIN

         IF @filecount>0

         BEGIN

            SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +

                    'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''

                    + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''

                    + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''

                    + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''

                    + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''

           

            IF @debug = 1 PRINT '@delcmd=' + @delcmd

            EXEC master.dbo.sp_executesql @delcmd

     

            SELECT @delcount = COUNT(*) from #files

         END

         ELSE

         BEGIN

            SELECT @delcount = 0

         END

      END

      ELSE  -- number of copies not date based (include current backup that's not in #files)

      BEGIN

         IF @filecount>0

         BEGIN

            IF @dbretainval>1

            BEGIN

               SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST((@dbretainval-1) as nvarchar(10)) +

                             N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'

     

               IF @debug = 1 PRINT '@delcmd=' + @delcmd

               EXEC master.dbo.sp_executesql @delcmd

            END

     

            SELECT @delcount = COUNT(*) from #files

  

         END

         ELSE

         BEGIN

            SELECT @delcount = 0

         END

      END

  

      IF @debug = 1 PRINT '@delcount = ' + STR(@delcount)

  

      -- if there are any matching files

      IF @filecount>0

      BEGIN

         -- are there any files that need deleting

         IF @delcount>0

         BEGIN

            DECLARE FCUR CURSOR FORWARD_ONLY FOR

            SELECT * FROM #files

            OPEN FCUR

            FETCH NEXT FROM FCUR INTO @delfilename

            WHILE @@FETCH_STATUS=0

            BEGIN

               SET @cmd = 'DEL /Q "' + @backupfldr + @delfilename + '"'

               EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output  

  

               -- log failure to delete but don't abort procedure

               IF @cmdret<>0

               BEGIN

                  SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @backupfldr + @delfilename + ' ***'

                  IF @debug = 1 PRINT @output

                  IF @report = 1

                  BEGIN

                     EXEC sp_OAMethod @file,'WriteLine',NULL,@output

                  END

                  SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)

               END

               ELSE

               BEGIN

                  SET @output = SPACE(4) + 'Deleted file ' + @backupfldr + @delfilename

                  IF @debug = 1 PRINT @output

                  IF @report = 1

                  BEGIN

                     EXEC sp_OAMethod @file,'WriteLine',NULL,@output

                  END

               END

  

               FETCH NEXT FROM FCUR INTO @delfilename

            END

            CLOSE FCUR

            DEALLOCATE FCUR

         END

      END

  

      -- write to text report

      SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'

      IF @debug = 1 PRINT @output

      IF @report = 1

      BEGIN

         EXEC sp_OAMethod @file,'WriteLine',NULL,@output

         EXEC sp_OAMethod @file,'WriteLine',NULL,''

      END

 

      FETCH NEXT FROM dcur into @database

   END

 

   CLOSE dcur

   DEALLOCATE dcur

 

   -- clear temporary table and variables

   DELETE #files

   SET @cmd = ''

   SET @delcmd = ''

   SET @delfilename = ''

   SET @datepart = ''

   SET @filecount = 0

   SET @delcount = 0

   SET @cmdret = 0

   SET @stage = @stage + 1

 

 

   /************************

      DELETE OLD REPORTS

   ************************/

 

   DELREPORTS:

 

   IF @rptretainunit IS NOT NULL

   BEGIN

      SET @datepart = CASE

         WHEN UPPER(@rptretainunit) = 'MINUTES' THEN N'mi'

         WHEN UPPER(@rptretainunit) = 'HOURS'   THEN N'hh'

         WHEN UPPER(@rptretainunit) = 'DAYS'    THEN N'dd'

         WHEN UPPER(@rptretainunit) = 'WEEKS'   THEN N'ww'

         WHEN UPPER(@rptretainunit) = 'MONTHS'  THEN N'yy'

   END

 

   IF @debug = 1 PRINT '@datepart for reports = ' + @datepart

 

   -- write to text report

   SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files...'

   IF @debug = 1 PRINT @output

   IF @report = 1

   BEGIN

      EXEC sp_OAMethod @file,'WriteLine',NULL,@output

   END

 

   -- load files in @reportfldr

   SET @cmd = 'dir /B /A-D /OD "' + @reportfldr + REPLACE(@databaseorig,' ','_') +

   CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'

        WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'

        WHEN UPPER(@optype) = 'REINDEX'  THEN '_Reindex_report_'    

        WHEN UPPER(@optype) = 'CHECKDB'  THEN '_CheckDB_report_'    

        WHEN UPPER(@optype) = 'REORG'  THEN '_Reorg_report_'

        WHEN UPPER(@optype) = 'LOG'  THEN '_LogBackup_report_' END + '*.txt"'

 

   IF @debug = 1 PRINT '@cmd = ' + @cmd

 

   INSERT #files EXEC master.dbo.xp_cmdshell @cmd

   DELETE #files WHERE filename IS NULL

 

   IF @debug = 1 SELECT * FROM #files

  

   -- get count of files that match pattern

   SELECT @filecount = COUNT(*) from #files

   WHERE PATINDEX('%File Not Found%',filename) = 0

   AND PATINDEX('%The system cannot find%',filename) = 0

 

   -- remove files that don't meet retention criteria if there are any files that match pattern

   IF UPPER(@rptretainunit) <> 'COPIES'

   BEGIN

      IF @filecount>0

      BEGIN

         SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@rptretainval as nvarchar(10)) + N',' +

                 'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''

                 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''

                 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''

                 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''

                 + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''

        

         IF @debug = 1 PRINT '@delcmd=' + @delcmd

         EXEC master.dbo.sp_executesql @delcmd

  

         SELECT @delcount = COUNT(*) from #files

      END

      ELSE

      BEGIN

         SELECT @delcount = 0

      END

   END

   ELSE  -- number of copies not date based

   BEGIN

      IF @filecount>0

      BEGIN

         SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST(@rptretainval as nvarchar(10)) +

                       N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'

 

         IF @debug = 1 PRINT '@delcmd=' + @delcmd

         EXEC master.dbo.sp_executesql @delcmd

  

         SELECT @delcount = COUNT(*) from #files

      END

      ELSE

      BEGIN

         SELECT @delcount = 0

      END

   END

  

   IF @debug = 1 PRINT STR(@delcount)

 

   -- if there are any matching files

   IF @filecount>0

   BEGIN

      -- are there any files that need deleting

      IF @delcount>0

      BEGIN

         DECLARE FCUR CURSOR FORWARD_ONLY FOR

         SELECT * FROM #files

         OPEN FCUR

         FETCH NEXT FROM FCUR INTO @delfilename

         WHILE @@FETCH_STATUS=0

         BEGIN

            SET @cmd = 'DEL /Q "' + @reportfldr + @delfilename + '"'

            EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output  

 

            -- log failure to delete but don't abort procedure

            IF @cmdret<>0

            BEGIN

 

               SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @reportfldr + @delfilename + ' ***'

               IF @debug = 1 PRINT @output

               IF @report = 1

               BEGIN

                  EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               END

               SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)

            END

            BEGIN

               SET @output = SPACE(4) + 'Deleted file ' + @reportfldr + @delfilename

               IF @debug = 1 PRINT @output

               IF @report = 1

               BEGIN

                  EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               END

            END

 

            FETCH NEXT FROM FCUR INTO @delfilename

         END

         CLOSE FCUR

         DEALLOCATE FCUR

      END

   END

 

   -- write to text report

   SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'

   IF @debug = 1 PRINT @output

   IF @report = 1

   BEGIN

      EXEC sp_OAMethod @file,'WriteLine',NULL,@output

      EXEC sp_OAMethod @file,'WriteLine',NULL,''

   END

 

   -- update stage

   SET @stage = @stage + 1

   END

   -- if we got here due to @delfirst = 1 go back and do the backups

   IF @delfirst = 1

      GOTO DOBACKUP

   ELSE

      GOTO CLEANUP

 

 

/************************

         CHECKDB

************************/

 

   CHECK_DB:

 

   IF @optype = 'CHECKDB'

   BEGIN

 

      DECLARE dcur CURSOR LOCAL FAST_FORWARD

      FOR SELECT dbname FROM #databases ORDER BY dbname

      OPEN dcur

      FETCH NEXT FROM dcur into @database

      WHILE @@FETCH_STATUS=0

      BEGIN

 

         -- write to text report

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

            SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Check Data and Index Linkage...'

            IF @debug = 1 PRINT @output

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output 

         END

 

         -- set backup start time

         SET @start = GETDATE()

 

         SET @execmd = N'DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS'

         IF @debug = 1 PRINT 'DBCC Command : ' + @execmd

        

         BEGIN TRY

 

            EXEC(@execmd)

 

         END TRY

         BEGIN CATCH

 

            SELECT @err = @@ERROR,@ret = @err

            SET @errormsg = 'CheckDB of ' + @database + ' failed with Native Error : ' + CAST(@err as varchar(10))

            SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'

            PRINT @output

            IF @report = 1

            BEGIN

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            END

            CLOSE dcur

            DEALLOCATE dcur

            GOTO CLEANUP

 

         END CATCH

        

         SET @finish = GETDATE()

 

         --calculate checkdb runtime

         SET @runtime = (@finish - @start)

         SET @output = SPACE(4) + 'CheckDB completed in '

                     + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '

                     + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '

                     + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

         END  

         SET @stage = (@stage + 1)

         FETCH NEXT FROM dcur into @database     

     

      END

 

      CLOSE dcur

      DEALLOCATE dcur

     

      -- delete reports

      IF @report = 1

      BEGIN

         EXEC sp_OAMethod @file,'WriteLine',NULL,''

      END

      GOTO DELREPORTS

   END

 

/************************

     REINDEX/REORG

************************/

 

   REINDEX:

 

   IF @optype in ('REINDEX','REORG')

   BEGIN

 

      DECLARE dcur CURSOR LOCAL FAST_FORWARD

      FOR SELECT dbname FROM #databases ORDER BY dbname

      OPEN dcur

      FETCH NEXT FROM dcur into @database

      WHILE @@FETCH_STATUS=0

      BEGIN

 

         -- write to text report

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

            IF @optype = 'REINDEX'

               SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Rebuild (using original fillfactor)...'

            ELSE

               SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Reorganize...'

 

            IF @debug = 1 PRINT @output

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output 

            EXEC sp_OAMethod @file,'WriteLine',NULL,'' 

         END

 

         -- set start time

         SET @start = GETDATE()

 

         -- all user tables

         CREATE TABLE #tables(tablename sysname)

         EXEC(N'INSERT #tables(tablename) SELECT DISTINCT(''['' + s.[name] + ''].['' + t.[name] + '']'') FROM [' + @database + N'].sys.tables t ' +

              N'JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ' +

              N'JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ' +

              N'WHERE t.is_ms_shipped = 0 AND i.type>0')

 

         DECLARE tcur CURSOR LOCAL FAST_FORWARD

         FOR SELECT tablename FROM #tables ORDER BY tablename

         OPEN tcur

         FETCH NEXT FROM tcur INTO @table

         WHILE @@FETCH_STATUS = 0

         BEGIN

 

            IF @report = 1

            BEGIN

               IF @optype = 'REINDEX'

                  SET @output = SPACE(4) + N'Rebuilding indexes for table ' + @table

               ELSE

                  SET @output = SPACE(4) + N'Reorganizing indexes for table ' + @table

 

               EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            END  

 

           

            IF @optype = 'REINDEX'

               SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REBUILD'

            ELSE

               SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REORGANIZE'

  

            IF @debug = 1 PRINT 'Reindex Command : ' + @execmd

 

            BEGIN TRY

  

               EXEC(@execmd)

  

            END TRY

            BEGIN CATCH

           

               SELECT @err = @@ERROR,@ret = @err

               SET @errormsg = 'Rebuild of indexes on [' + @database + N'].' + @table + ' failed with Native Error : ' + CAST(@err as varchar(10))

               SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'

               PRINT @output

               IF @report = 1

               BEGIN

                  EXEC sp_OAMethod @file,'WriteLine',NULL,@output

               END

               CLOSE tcur

               DEALLOCATE tcur

               DROP TABLE #tables

               GOTO CLEANUP

  

            END CATCH

 

            FETCH NEXT FROM tcur INTO @table

         END

 

         CLOSE tcur

         DEALLOCATE tcur

   

         SET @finish = GETDATE()

 

         --calculate runtime

         SET @runtime = (@finish - @start)

         SET @output = SPACE(4) + 'Index maintenance completed in '

                     + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '

                     + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '

                     + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'

         IF @debug = 1 PRINT @output

         IF @report = 1

         BEGIN

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

            EXEC sp_OAMethod @file,'WriteLine',NULL,@output

            EXEC sp_OAMethod @file,'WriteLine',NULL,''

         END  

 

         DROP TABLE #tables

 

         SET @stage = (@stage + 1)

         FETCH NEXT FROM dcur into @database  

     

      END

 

      CLOSE dcur

      DEALLOCATE dcur

     

      -- delete reports

      GOTO DELREPORTS

   END

 

     

/************************

         CLEAN UP

************************/

 

   CLEANUP:

 

   DROP TABLE #files

   DROP TABLE #exists

   DROP TABLE #databases

 

   -- if we encountered errors deleting old backups return failure

   IF @delbkflag<>0

   BEGIN

      SET @errormsg = 'Expressmaint encountered errors deleting old backup files' + CHAR(13)

                    + CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END

      RAISERROR(@errormsg,16,1)

      SET @ret = 1

   END

 

   -- if we encountered errors deleting old reports return failure

   IF (@delrptflag<>0 AND @delbkflag = 0)

   BEGIN

      SET @errormsg = 'Expressmaint encountered errors deleting old report files' + CHAR(13)

                    + CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END

      RAISERROR(@errormsg,16,1)

      SET @ret = 1

   END

  

   -- if we created a file make sure we write trailer and destroy object

   IF @filecrt = 1

   BEGIN

      -- write final part of report

      EXEC sp_OAMethod @file,'WriteLine',NULL,''

      SET @output = 'Expressmaint processing finished at ' + CONVERT(varchar(25),GETDATE(),100)

                  + ' (Return Code : ' + CAST(@ret as varchar(10)) + ')'

      IF @debug = 1 PRINT @output

      EXEC sp_OAMethod @file,'WriteLine',NULL,@output

      EXEC sp_OAMethod @file,'WriteLine',NULL,''

 

      -- destroy file object

      EXEC @hr=sp_OADestroy @file

      IF @hr <> 0 EXEC sp_OAGetErrorInfo @file

   END

 

   IF @report = 1

   BEGIN

      EXEC @hr=sp_OADestroy @fso

      IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

   END

 

RETURN @ret

 

Go

 

USE [master]

GO

/****** Object:  StoredProcedure [DBO].[spIndexOptimize]    Script Date: 13/02/2017 11:31:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

if OBJECT_ID('[DBO].[spIndexOptimize]') > 0

                drop proc [DBO].[spIndexOptimize]

go          

 

CREATE PROCEDURE [DBO].[spIndexOptimize]

 

@Databases nvarchar(max),

@FragmentationLow nvarchar(max) = NULL,

@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 int = 5,

@FragmentationLevel2 int = 30,

@PageCountLevel int = 1000,

@SortInTempdb nvarchar(max) = 'N',

@MaxDOP int = NULL,

@FillFactor int = NULL,

@PadIndex nvarchar(max) = NULL,

@LOBCompaction nvarchar(max) = 'Y',

@UpdateStatistics nvarchar(max) = NULL,

@OnlyModifiedStatistics nvarchar(max) = 'N',

@StatisticsSample int = NULL,

@StatisticsResample nvarchar(max) = 'N',

@PartitionLevel nvarchar(max) = 'Y',

@MSShippedObjects nvarchar(max) = 'N',

@Indexes nvarchar(max) = NULL,

@TimeLimit int = NULL,

@Delay int = NULL,

@WaitAtLowPriorityMaxDuration int = NULL,

@WaitAtLowPriorityAbortAfterWait nvarchar(max) = NULL,

@LockTimeout int = NULL,

@LogToTable nvarchar(max) = 'N',

@Execute nvarchar(max) = 'Y'

 

AS

 

BEGIN

 

/**********************************************************

 

Source: https://ola.hallengren.com

 

Usage:

 

exec DBO.spIndexOptimize @Databases = 'ALL_DATABASES'

exec DBO.spIndexOptimize @Databases = 'TDW'

 

************************************************************/

 

  SET NOCOUNT ON

 

  SET ARITHABORT ON

 

  SET NUMERIC_ROUNDABORT OFF

 

  DECLARE @StartMessage nvarchar(max)

  DECLARE @EndMessage nvarchar(max)

  DECLARE @DatabaseMessage nvarchar(max)

  DECLARE @ErrorMessage nvarchar(max)

 

  DECLARE @Version numeric(18,10)

  DECLARE @AmazonRDS bit

 

  DECLARE @Cluster nvarchar(max)

 

  DECLARE @StartTime datetime

 

  DECLARE @CurrentDBID int

  DECLARE @CurrentDatabaseID int

  DECLARE @CurrentDatabaseName nvarchar(max)

  DECLARE @CurrentIsDatabaseAccessible bit

  DECLARE @CurrentAvailabilityGroup nvarchar(max)

  DECLARE @CurrentAvailabilityGroupRole nvarchar(max)

  DECLARE @CurrentDatabaseMirroringRole nvarchar(max)

 

  DECLARE @CurrentCommand01 nvarchar(max)

  DECLARE @CurrentCommand02 nvarchar(max)

  DECLARE @CurrentCommand03 nvarchar(max)

  DECLARE @CurrentCommand04 nvarchar(max)

  DECLARE @CurrentCommand05 nvarchar(max)

  DECLARE @CurrentCommand06 nvarchar(max)

  DECLARE @CurrentCommand07 nvarchar(max)

  DECLARE @CurrentCommand08 nvarchar(max)

  DECLARE @CurrentCommand09 nvarchar(max)

  DECLARE @CurrentCommand10 nvarchar(max)

  DECLARE @CurrentCommand11 nvarchar(max)

  DECLARE @CurrentCommand12 nvarchar(max)

  DECLARE @CurrentCommand13 nvarchar(max)

  DECLARE @CurrentCommand14 nvarchar(max)

 

  DECLARE @CurrentCommandOutput13 int

  DECLARE @CurrentCommandOutput14 int

 

  DECLARE @CurrentCommandType13 nvarchar(max)

  DECLARE @CurrentCommandType14 nvarchar(max)

 

  DECLARE @CurrentIxID int

  DECLARE @CurrentSchemaID int

  DECLARE @CurrentSchemaName nvarchar(max)

  DECLARE @CurrentObjectID int

  DECLARE @CurrentObjectName nvarchar(max)

  DECLARE @CurrentObjectType nvarchar(max)

  DECLARE @CurrentIsMemoryOptimized bit

  DECLARE @CurrentIndexID int

  DECLARE @CurrentIndexName nvarchar(max)

  DECLARE @CurrentIndexType int

  DECLARE @CurrentStatisticsID int

  DECLARE @CurrentStatisticsName nvarchar(max)

  DECLARE @CurrentPartitionID bigint

  DECLARE @CurrentPartitionNumber int

  DECLARE @CurrentPartitionCount int

  DECLARE @CurrentIsPartition bit

  DECLARE @CurrentIndexExists bit

  DECLARE @CurrentStatisticsExists bit

  DECLARE @CurrentIsImageText bit

  DECLARE @CurrentIsNewLOB bit

  DECLARE @CurrentIsFileStream bit

  DECLARE @CurrentIsColumnStore bit

  DECLARE @CurrentAllowPageLocks bit

  DECLARE @CurrentNoRecompute bit

  DECLARE @CurrentStatisticsModified bit

  DECLARE @CurrentOnReadOnlyFileGroup bit

  DECLARE @CurrentFragmentationLevel float

  DECLARE @CurrentPageCount bigint

  DECLARE @CurrentFragmentationGroup nvarchar(max)

  DECLARE @CurrentAction nvarchar(max)

  DECLARE @CurrentMaxDOP int

  DECLARE @CurrentUpdateStatistics nvarchar(max)

  DECLARE @CurrentComment nvarchar(max)

  DECLARE @CurrentExtendedInfo xml

  DECLARE @CurrentDelay datetime

 

  DECLARE @tmpDatabases TABLE (ID int IDENTITY,

                               DatabaseName nvarchar(max),

                               DatabaseType nvarchar(max),

                               Selected bit,

                               Completed bit,

                               PRIMARY KEY(Selected, Completed, ID))

 

  DECLARE @tmpIndexesStatistics TABLE (ID int IDENTITY,

                                       SchemaID int,

                                       SchemaName nvarchar(max),

                                       ObjectID int,

                                       ObjectName nvarchar(max),

                                       ObjectType nvarchar(max),

                                       IsMemoryOptimized bit,

                                       IndexID int,

                                       IndexName nvarchar(max),

                                       IndexType int,

                                       StatisticsID int,

                                       StatisticsName nvarchar(max),

                                       PartitionID bigint,

                                       PartitionNumber int,

                                       PartitionCount int,

                                       Selected bit,

                                       Completed bit,

                                       PRIMARY KEY(Selected, Completed, ID))

 

  DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),

                                    DatabaseType nvarchar(max),

                                    Selected bit)

 

  DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),

                                  SchemaName nvarchar(max),

                                  ObjectName nvarchar(max),

                                  IndexName nvarchar(max),

                                  Selected bit)

 

  DECLARE @Actions TABLE ([Action] nvarchar(max))

 

  INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')

  INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')

  INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')

 

  DECLARE @ActionsPreferred TABLE (FragmentationGroup nvarchar(max),

                                   [Priority] int,

                                   [Action] nvarchar(max))

 

  DECLARE @CurrentActionsAllowed TABLE ([Action] nvarchar(max))

 

  DECLARE @Error int

  DECLARE @ReturnCode int

 

  SET @Error = 0

  SET @ReturnCode = 0

 

  SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

 

  SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

 

  ----------------------------------------------------------------------------------------------------

  --// Log initial information                                                                    //--

  ----------------------------------------------------------------------------------------------------

 

  SET @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)

 

  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @PadIndex = ' + ISNULL('''' + REPLACE(@PadIndex,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @UpdateStatistics = ' + ISNULL('''' + REPLACE(@UpdateStatistics,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @OnlyModifiedStatistics = ' + ISNULL('''' + REPLACE(@OnlyModifiedStatistics,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @StatisticsResample = ' + ISNULL('''' + REPLACE(@StatisticsResample,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @MSShippedObjects = ' + ISNULL('''' + REPLACE(@MSShippedObjects,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @Indexes = ' + ISNULL('''' + REPLACE(@Indexes,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @Delay = ' + ISNULL(CAST(@Delay AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @WaitAtLowPriorityMaxDuration = ' + ISNULL(CAST(@WaitAtLowPriorityMaxDuration AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @WaitAtLowPriorityAbortAfterWait = ' + ISNULL('''' + REPLACE(@WaitAtLowPriorityAbortAfterWait,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')

  SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')

  SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10)

  SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '

  RAISERROR(@StartMessage,10,1) WITH NOWAIT

 

  ----------------------------------------------------------------------------------------------------

  --// Check core requirements                                                                    //--

  ----------------------------------------------------------------------------------------------------

 

  IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'DBO' AND objects.[name] = 'spCommandExecute')

  BEGIN

    SET @ErrorMessage = 'The stored procedure spCommandExecute is missing. Download https://ola.hallengren.com/scripts/spCommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'DBO' AND objects.[name] = 'spCommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))

  BEGIN

    SET @ErrorMessage = 'The stored procedure spCommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/spCommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')

  BEGIN

    SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF SERVERPROPERTY('EngineEdition') = 5 AND @Version < 12

  BEGIN

    SET @ErrorMessage = 'The stored procedure spIndexOptimize is not supported on this version of Azure SQL Database.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO Logging

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Select databases                                                                           //--

  ----------------------------------------------------------------------------------------------------

 

  SET @Databases = REPLACE(@Databases, ', ', ',');

 

  WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS

  (

  SELECT 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,

         SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem

  WHERE @Databases IS NOT NULL

  UNION ALL

  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,

         SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem

  FROM Databases1

  WHERE EndPosition < LEN(@Databases) + 1

  ),

  Databases2 (DatabaseItem, Selected) AS

  (

  SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,

         CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected

  FROM Databases1

  ),

  Databases3 (DatabaseItem, DatabaseType, Selected) AS

  (

  SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,

         CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,

         Selected

  FROM Databases2

  ),

  Databases4 (DatabaseName, DatabaseType, Selected) AS

  (

  SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,

         DatabaseType,

         Selected

  FROM Databases3

  )

  INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)

  SELECT DatabaseName,

         DatabaseType,

         Selected

  FROM Databases4

  OPTION (MAXRECURSION 0)

 

  INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, Selected, Completed)

  SELECT [name] AS DatabaseName,

         CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,

         0 AS Selected,

         0 AS Completed

  FROM sys.databases

  WHERE [name] <> 'tempdb'

  AND source_database_id IS NULL

  ORDER BY [name] ASC

 

  UPDATE tmpDatabases

  SET tmpDatabases.Selected = SelectedDatabases.Selected

  FROM @tmpDatabases tmpDatabases

  INNER JOIN @SelectedDatabases SelectedDatabases

  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')

  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)

  WHERE SelectedDatabases.Selected = 1

 

  UPDATE tmpDatabases

  SET tmpDatabases.Selected = SelectedDatabases.Selected

  FROM @tmpDatabases tmpDatabases

  INNER JOIN @SelectedDatabases SelectedDatabases

  ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')

  AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)

  WHERE SelectedDatabases.Selected = 0

 

  IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Select indexes                                                                             //--

  ----------------------------------------------------------------------------------------------------

 

  SET @Indexes = REPLACE(@Indexes, ', ', ',');

 

  WITH Indexes1 (StartPosition, EndPosition, IndexItem) AS

  (

  SELECT 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) AS EndPosition,

         SUBSTRING(@Indexes, 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) - 1) AS IndexItem

  WHERE @Indexes IS NOT NULL

  UNION ALL

  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) AS EndPosition,

         SUBSTRING(@Indexes, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) - EndPosition - 1) AS IndexItem

  FROM Indexes1

  WHERE EndPosition < LEN(@Indexes) + 1

  ),

  Indexes2 (IndexItem, Selected) AS

  (

  SELECT CASE WHEN IndexItem LIKE '-%' THEN RIGHT(IndexItem,LEN(IndexItem) - 1) ELSE IndexItem END AS IndexItem,

         CASE WHEN IndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected

  FROM Indexes1

  ),

  Indexes3 (IndexItem, Selected) AS

  (

  SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,

         Selected

  FROM Indexes2

  ),

  Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, Selected) AS

  (

  SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,

         CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,

         CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,

         CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,

         Selected

  FROM Indexes3

  )

  INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, Selected)

  SELECT DatabaseName, SchemaName, ObjectName, IndexName, Selected

  FROM Indexes4

  OPTION (MAXRECURSION 0);

 

  ----------------------------------------------------------------------------------------------------

  --// Select actions                                                                             //--

  ----------------------------------------------------------------------------------------------------

 

  WITH FragmentationLow (StartPosition, EndPosition, [Action]) AS

  (

  SELECT 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,

         SUBSTRING(@FragmentationLow, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) - 1) AS [Action]

  WHERE @FragmentationLow IS NOT NULL

  UNION ALL

  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,

         SUBSTRING(@FragmentationLow, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) - EndPosition - 1) AS [Action]

  FROM FragmentationLow

  WHERE EndPosition < LEN(@FragmentationLow) + 1

  )

  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])

  SELECT 'Low' AS FragmentationGroup,

         ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],

         [Action]

  FROM FragmentationLow

  OPTION (MAXRECURSION 0);

 

  WITH FragmentationMedium (StartPosition, EndPosition, [Action]) AS

  (

  SELECT 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,

         SUBSTRING(@FragmentationMedium, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) - 1) AS [Action]

  WHERE @FragmentationMedium IS NOT NULL

  UNION ALL

  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,

         SUBSTRING(@FragmentationMedium, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) - EndPosition - 1) AS [Action]

  FROM FragmentationMedium

  WHERE EndPosition < LEN(@FragmentationMedium) + 1

  )

  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])

  SELECT 'Medium' AS FragmentationGroup,

         ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],

         [Action]

  FROM FragmentationMedium

  OPTION (MAXRECURSION 0);

 

  WITH FragmentationHigh (StartPosition, EndPosition, [Action]) AS

  (

  SELECT 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,

         SUBSTRING(@FragmentationHigh, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) - 1) AS [Action]

  WHERE @FragmentationHigh IS NOT NULL

  UNION ALL

  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

         ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,

         SUBSTRING(@FragmentationHigh, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) - EndPosition - 1) AS [Action]

  FROM FragmentationHigh

  WHERE EndPosition < LEN(@FragmentationHigh) + 1

  )

  INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])

  SELECT 'High' AS FragmentationGroup,

         ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],

         [Action]

  FROM FragmentationHigh

  OPTION (MAXRECURSION 0)

 

  ----------------------------------------------------------------------------------------------------

  --// Check input parameters                                                                     //--

  ----------------------------------------------------------------------------------------------------

 

  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' AND [Action] NOT IN(SELECT * FROM @Actions))

  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' GROUP BY [Action] HAVING COUNT(*) > 1)

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @FragmentationLow is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' AND [Action] NOT IN(SELECT * FROM @Actions))

  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' GROUP BY [Action] HAVING COUNT(*) > 1)

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @FragmentationMedium is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'High' AND [Action] NOT IN(SELECT * FROM @Actions))

  OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'High' GROUP BY [Action] HAVING COUNT(*) > 1)

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @FragmentationHigh is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @PageCountLevel < 0 OR @PageCountLevel IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @SortInTempdb is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @MaxDOP < 0 OR @MaxDOP > 64 OR @MaxDOP > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5))

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @FillFactor <= 0 OR @FillFactor > 100

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @FillFactor is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @PadIndex NOT IN('Y','N')

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @PadIndex is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @LOBCompaction NOT IN('Y','N') OR @LOBCompaction IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @LOBCompaction is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @UpdateStatistics NOT IN('ALL','COLUMNS','INDEX')

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @UpdateStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @OnlyModifiedStatistics NOT IN('Y','N') OR @OnlyModifiedStatistics IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @OnlyModifiedStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @StatisticsSample <= 0 OR @StatisticsSample  > 100

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @StatisticsSample is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @StatisticsResample NOT IN('Y','N') OR @StatisticsResample IS NULL OR (@StatisticsResample = 'Y' AND @StatisticsSample IS NOT NULL)

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @StatisticsResample is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @PartitionLevel NOT IN('Y','N') OR @PartitionLevel IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @PartitionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @MSShippedObjects NOT IN('Y','N') OR @MSShippedObjects IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @MSShippedObjects is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL) OR (@Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes))

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Indexes is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @TimeLimit < 0

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @TimeLimit is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Delay < 0

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Delay is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @WaitAtLowPriorityMaxDuration < 0 OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @WaitAtLowPriorityAbortAfterWait IS NULL) OR (@WaitAtLowPriorityMaxDuration IS NULL AND @WaitAtLowPriorityAbortAfterWait IS NOT NULL)

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @WaitAtLowPriorityAbortAfterWait NOT IN('NONE','SELF','BLOCKERS') OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @WaitAtLowPriorityMaxDuration IS NULL) OR (@WaitAtLowPriorityAbortAfterWait IS NULL AND @WaitAtLowPriorityMaxDuration IS NOT NULL)

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityAbortAfterWait is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @LockTimeout < 0

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @LockTimeout is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Execute NOT IN('Y','N') OR @Execute IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Error <> 0

  BEGIN

    SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @ReturnCode = @Error

    GOTO Logging

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Check Availability Group cluster name                                                      //--

  ----------------------------------------------------------------------------------------------------

 

  IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5

  BEGIN

    SELECT @Cluster = cluster_name

    FROM sys.dm_hadr_cluster

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Execute commands                                                                           //--

  ----------------------------------------------------------------------------------------------------

 

  WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)

  BEGIN

 

    SELECT TOP 1 @CurrentDBID = ID,

                 @CurrentDatabaseName = DatabaseName

    FROM @tmpDatabases

    WHERE Selected = 1

    AND Completed = 0

    ORDER BY ID ASC

 

    SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)

 

    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5

    BEGIN

      IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)

      BEGIN

        SET @CurrentIsDatabaseAccessible = 1

      END

      ELSE

      BEGIN

        SET @CurrentIsDatabaseAccessible = 0

      END

    END

 

    IF @Version >= 11 AND @Cluster IS NOT NULL

    BEGIN

      SELECT @CurrentAvailabilityGroup = availability_groups.name,

             @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc

      FROM sys.databases databases

      INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id

      INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id

      INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id

      WHERE databases.name = @CurrentDatabaseName

    END

 

    IF SERVERPROPERTY('EngineEdition') <> 5

    BEGIN

      SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)

      FROM sys.database_mirroring

      WHERE database_id = @CurrentDatabaseID

    END

 

    -- Set database message

    SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)

    IF @CurrentIsDatabaseAccessible IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)

    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)

    IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)

    IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)

    SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '

    RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT

 

    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'

    AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)

    AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'

    BEGIN

 

      -- Select indexes in the current database

      IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)

      BEGIN

        SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed FROM ('

 

        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')

        BEGIN

          SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE 'NULL' END + ' AS IsMemoryOptimized, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName'

          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount'

          IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount'

          SET @CurrentCommand01 = @CurrentCommand01 + ', 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.[object_id] = tables.[object_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'

          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(*) AS partition_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'

          IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id] AND partitions.partition_id = dm_db_partition_stats.partition_id'

          IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN (SELECT dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id], SUM(dm_db_partition_stats.in_row_data_page_count) AS in_row_data_page_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats GROUP BY dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id]) dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id]'

          SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'

          IF (@UpdateStatistics NOT IN('ALL','INDEX') OR @UpdateStatistics IS NULL) AND @PageCountLevel > 0 SET @CurrentCommand01 = @CurrentCommand01 + ' AND (dm_db_partition_stats.in_row_data_page_count >= @ParamPageCountLevel OR dm_db_partition_stats.in_row_data_page_count IS NULL)'

          IF NOT EXISTS(SELECT * FROM @ActionsPreferred) SET @CurrentCommand01 = @CurrentCommand01 + ' AND stats.stats_id IS NOT NULL'

        END

 

        IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ' UNION '

 

        IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE 'NULL' END + ' AS IsMemoryOptimized, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.[object_id] = tables.[object_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND NOT EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)'

 

        SET @CurrentCommand01 = @CurrentCommand01 + ') IndexesStatistics ORDER BY SchemaName ASC, ObjectName ASC'

        IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ', CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC'

        IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX') SET @CurrentCommand01 = @CurrentCommand01 + ', IndexType ASC, IndexName ASC'

        IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + ', StatisticsName ASC'

        IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PartitionNumber ASC'

 

        INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed)

        EXECUTE sp_executesql @statement = @CurrentCommand01, @params = N'@ParamPageCountLevel int', @ParamPageCountLevel = @PageCountLevel

        SET @Error = @@ERROR

        IF @Error <> 0

        BEGIN

          SET @ReturnCode = @Error

        END

      END

 

      IF @Indexes IS NULL

      BEGIN

        UPDATE tmpIndexesStatistics

        SET tmpIndexesStatistics.Selected = 1

        FROM @tmpIndexesStatistics tmpIndexesStatistics

      END

      ELSE

      BEGIN

        UPDATE tmpIndexesStatistics

        SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected

        FROM @tmpIndexesStatistics tmpIndexesStatistics

        INNER JOIN @SelectedIndexes SelectedIndexes

        ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')

        WHERE SelectedIndexes.Selected = 1

 

        UPDATE tmpIndexesStatistics

        SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected

        FROM @tmpIndexesStatistics tmpIndexesStatistics

        INNER JOIN @SelectedIndexes SelectedIndexes

        ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')

        WHERE SelectedIndexes.Selected = 0

      END

 

      WHILE EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE Selected = 1 AND Completed = 0 AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL))

      BEGIN

 

        SELECT TOP 1 @CurrentIxID = ID,

                     @CurrentSchemaID = SchemaID,

                     @CurrentSchemaName = SchemaName,

                     @CurrentObjectID = ObjectID,

                     @CurrentObjectName = ObjectName,

                     @CurrentObjectType = ObjectType,

                     @CurrentIsMemoryOptimized = IsMemoryOptimized,

                     @CurrentIndexID = IndexID,

                     @CurrentIndexName = IndexName,

                     @CurrentIndexType = IndexType,

                     @CurrentStatisticsID = StatisticsID,

                     @CurrentStatisticsName = StatisticsName,

                     @CurrentPartitionID = PartitionID,

                     @CurrentPartitionNumber = PartitionNumber,

                     @CurrentPartitionCount = PartitionCount

        FROM @tmpIndexesStatistics

        WHERE Selected = 1

        AND Completed = 0

        ORDER BY ID ASC

 

        -- Is the index a partition?

        IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1 BEGIN SET @CurrentIsPartition = 0 END ELSE BEGIN SET @CurrentIsPartition = 1 END

 

        -- Does the index exist?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand02 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand02 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          IF @CurrentIsPartition = 0 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType) BEGIN SET @ParamIndexExists = 1 END'

          IF @CurrentIsPartition = 1 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType AND partitions.partition_id = @ParamPartitionID AND partitions.partition_number = @ParamPartitionNumber) BEGIN SET @ParamIndexExists = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand02, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamIndexID int, @ParamIndexName sysname, @ParamIndexType int, @ParamPartitionID bigint, @ParamPartitionNumber int, @ParamIndexExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamIndexID = @CurrentIndexID, @ParamIndexName = @CurrentIndexName, @ParamIndexType = @CurrentIndexType, @ParamPartitionID = @CurrentPartitionID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamIndexExists = @CurrentIndexExists OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIndexExists IS NULL SET @CurrentIndexExists = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index exists.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

          IF @CurrentIndexExists = 0 GOTO NoAction

        END

 

        -- Does the statistics exist?

        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL

        BEGIN

          SET @CurrentCommand03 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand03 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand03 = @CurrentCommand03 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND stats.stats_id = @ParamStatisticsID AND stats.[name] = @ParamStatisticsName) BEGIN SET @ParamStatisticsExists = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamStatisticsID int, @ParamStatisticsName sysname, @ParamStatisticsExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsName = @CurrentStatisticsName, @ParamStatisticsExists = @CurrentStatisticsExists OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentStatisticsExists IS NULL SET @CurrentStatisticsExists = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the statistics exists.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

          IF @CurrentStatisticsExists = 0 GOTO NoAction

        END

 

        -- Is one of the columns in the index an image, text or ntext data type?

        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType = 1 AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand04 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand04 = @CurrentCommand04 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = @ParamObjectID AND types.name IN(''image'',''text'',''ntext'')) BEGIN SET @ParamIsImageText = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand04, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsImageText bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsImageText = @CurrentIsImageText OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsImageText IS NULL SET @CurrentIsImageText = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any image, text, or ntext data types.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is one of the columns in the index an xml, varchar(max), nvarchar(max), varbinary(max) or large CLR data type?

        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1,2) AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand05 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          IF @CurrentIndexType = 1 SET @CurrentCommand05 = @CurrentCommand05 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = @ParamObjectID AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'

          IF @CurrentIndexType = 2 SET @CurrentCommand05 = @CurrentCommand05 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.index_columns index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = @ParamObjectID AND index_columns.index_id = @ParamIndexID AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand05, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsNewLOB bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsNewLOB = @CurrentIsNewLOB OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsNewLOB IS NULL SET @CurrentIsNewLOB = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any xml, varchar(max), nvarchar(max), varbinary(max), or large CLR data types.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is one of the columns in the index a file stream column?

        IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType = 1 AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand06 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand06 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand06 = @CurrentCommand06 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns WHERE columns.[object_id] = @ParamObjectID  AND columns.is_filestream = 1) BEGIN SET @ParamIsFileStream = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand06, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsFileStream bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsFileStream = @CurrentIsFileStream OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsFileStream IS NULL SET @CurrentIsFileStream = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any file stream columns.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is there a columnstore index on the table?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred) AND @Version >= 11

        BEGIN

          SET @CurrentCommand07 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand07 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand07 = @CurrentCommand07 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND [type] IN(5,6)) BEGIN SET @ParamIsColumnStore = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand07, @params = N'@ParamObjectID int, @ParamIsColumnStore bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIsColumnStore = @CurrentIsColumnStore OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentIsColumnStore IS NULL SET @CurrentIsColumnStore = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if there is a columnstore index on the table.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is Allow_Page_Locks set to On?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand08 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand08 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand08 = @CurrentCommand08 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID AND indexes.[allow_page_locks] = 1) BEGIN SET @ParamAllowPageLocks = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand08, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamAllowPageLocks bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamAllowPageLocks = @CurrentAllowPageLocks OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentAllowPageLocks IS NULL SET @CurrentAllowPageLocks = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if page locking is enabled on the index.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is No_Recompute set to On?

        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL

        BEGIN

          SET @CurrentCommand09 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand09 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand09 = @CurrentCommand09 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats WHERE stats.[object_id] = @ParamObjectID AND stats.[stats_id] = @ParamStatisticsID AND stats.[no_recompute] = 1) BEGIN SET @ParamNoRecompute = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand09, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamNoRecompute bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamNoRecompute = @CurrentNoRecompute OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentNoRecompute IS NULL SET @CurrentNoRecompute = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if automatic statistics update is enabled.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Has the data in the statistics been modified since the statistics was last updated?

        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL AND @OnlyModifiedStatistics = 'Y'

        BEGIN

          SET @CurrentCommand10 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand10 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          IF (@Version >= 10.504000 AND @Version < 11) OR @Version >= 11.03000

          BEGIN

            SET @CurrentCommand10 = @CurrentCommand10 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; IF EXISTS(SELECT * FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE modification_counter > 0) BEGIN SET @ParamStatisticsModified = 1 END'

          END

          ELSE

          BEGIN

            SET @CurrentCommand10 = @CurrentCommand10 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID AND sysindexes.[rowmodctr] <> 0) BEGIN SET @ParamStatisticsModified = 1 END'

          END

 

          EXECUTE sp_executesql @statement = @CurrentCommand10, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamStatisticsModified bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsModified = @CurrentStatisticsModified OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentStatisticsModified IS NULL SET @CurrentStatisticsModified = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if any rows has been modified since the most recent statistics update.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is the index on a read-only filegroup?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentCommand11 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand11 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand11 = @CurrentCommand11 + 'IF EXISTS(SELECT * FROM (SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'

          IF @CurrentIsPartition = 1 SET @CurrentCommand11 = @CurrentCommand11 + ' AND destination_data_spaces.destination_id = @ParamPartitionNumber'

          SET @CurrentCommand11 = @CurrentCommand11 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'

          IF @CurrentIndexType = 1 SET @CurrentCommand11 = @CurrentCommand11 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = @ParamObjectID'

          SET @CurrentCommand11 = @CurrentCommand11 + ') ReadOnlyFileGroups) BEGIN SET @ParamOnReadOnlyFileGroup = 1 END'

 

          EXECUTE sp_executesql @statement = @CurrentCommand11, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamOnReadOnlyFileGroup bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamOnReadOnlyFileGroup = @CurrentOnReadOnlyFileGroup OUTPUT

          SET @Error = @@ERROR

          IF @Error = 0 AND @CurrentOnReadOnlyFileGroup IS NULL SET @CurrentOnReadOnlyFileGroup = 0

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index is on a read-only filegroup.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Is the index fragmented?

        IF @CurrentIndexID IS NOT NULL

        AND @CurrentOnReadOnlyFileGroup = 0

        AND EXISTS(SELECT * FROM @ActionsPreferred)

        AND (EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3) OR @PageCountLevel > 0)

        BEGIN

          SET @CurrentCommand12 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand12 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand12 = @CurrentCommand12 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'

 

          EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamDatabaseID int, @ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT', @ParamDatabaseID = @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount = @CurrentPageCount OUTPUT

          SET @Error = @@ERROR

          IF @Error = 1222

          BEGIN

            SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. The size and fragmentation of the index could not be checked.' + CHAR(13) + CHAR(10) + ' '

            SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

          END

          IF @Error <> 0

          BEGIN

            SET @ReturnCode = @Error

            GOTO NoAction

          END

        END

 

        -- Select fragmentation group

        IF @CurrentIndexID IS NOT NULL AND @CurrentOnReadOnlyFileGroup = 0 AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          SET @CurrentFragmentationGroup = CASE

          WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN 'High'

          WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'

          WHEN @CurrentFragmentationLevel < @FragmentationLevel1 THEN 'Low'

          END

        END

 

        -- Which actions are allowed?

        IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)

        BEGIN

          IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)

          BEGIN

            INSERT INTO @CurrentActionsAllowed ([Action])

            VALUES ('INDEX_REORGANIZE')

          END

          IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)

          BEGIN

            INSERT INTO @CurrentActionsAllowed ([Action])

            VALUES ('INDEX_REBUILD_OFFLINE')

          END

          IF @CurrentOnReadOnlyFileGroup = 0

          AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)

          AND (@CurrentIsPartition = 0 OR @Version >= 12)

          AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)

          OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)

          OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)

          OR (@CurrentIndexType = 2 AND @Version >= 11))

          AND (@CurrentIsColumnStore = 0 OR @Version < 11)

          AND SERVERPROPERTY('EngineEdition') IN (3,5)

          BEGIN

            INSERT INTO @CurrentActionsAllowed ([Action])

            VALUES ('INDEX_REBUILD_ONLINE')

          END

        END

 

        -- Decide action

        IF @CurrentIndexID IS NOT NULL

        AND EXISTS(SELECT * FROM @ActionsPreferred)

        AND (@CurrentPageCount >= @PageCountLevel OR @PageCountLevel = 0)

        BEGIN

          IF EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3)

          BEGIN

            SELECT @CurrentAction = [Action]

            FROM @ActionsPreferred

            WHERE FragmentationGroup = @CurrentFragmentationGroup

            AND [Priority] = (SELECT MIN([Priority])

                              FROM @ActionsPreferred

                              WHERE FragmentationGroup = @CurrentFragmentationGroup

                              AND [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))

          END

          ELSE

          BEGIN

            SELECT @CurrentAction = [Action]

            FROM @ActionsPreferred

            WHERE [Priority] = (SELECT MIN([Priority])

                                FROM @ActionsPreferred

                                WHERE [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))

          END

        END

 

        -- Workaround for limitation in SQL Server, https://support.microsoft.com/kb/2292737

        IF @CurrentIndexID IS NOT NULL

        BEGIN

          SET @CurrentMaxDOP = @MaxDOP

          IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0

          BEGIN

            SET @CurrentMaxDOP = 1

          END

        END

 

        -- Update statistics?

        IF @CurrentStatisticsID IS NOT NULL

        AND ((@UpdateStatistics = 'ALL' AND (@CurrentIndexType IN (1,2,3,4,7) OR @CurrentIndexID IS NULL)) OR (@UpdateStatistics = 'INDEX' AND @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN (1,2,3,4,7)) OR (@UpdateStatistics = 'COLUMNS' AND @CurrentIndexID IS NULL))

        AND (@CurrentStatisticsModified = 1 OR @OnlyModifiedStatistics = 'N' OR @CurrentIsMemoryOptimized = 1)

        AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND @CurrentPartitionNumber = @CurrentPartitionCount))

        BEGIN

          SET @CurrentUpdateStatistics = 'Y'

        END

        ELSE

        BEGIN

          SET @CurrentUpdateStatistics = 'N'

        END

 

        -- Create comment

        IF @CurrentIndexID IS NOT NULL

        BEGIN

          SET @CurrentComment = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' WHEN @CurrentIndexType = 5 THEN 'Clustered Columnstore' WHEN @CurrentIndexType = 6 THEN 'NonClustered Columnstore' WHEN @CurrentIndexType = 7 THEN 'NonClustered Hash' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'ImageText: ' + CASE WHEN @CurrentIsImageText = 1 THEN 'Yes' WHEN @CurrentIsImageText = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'NewLOB: ' + CASE WHEN @CurrentIsNewLOB = 1 THEN 'Yes' WHEN @CurrentIsNewLOB = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'FileStream: ' + CASE WHEN @CurrentIsFileStream = 1 THEN 'Yes' WHEN @CurrentIsFileStream = 0 THEN 'No' ELSE 'N/A' END + ', '

          IF @Version >= 11 SET @CurrentComment = @CurrentComment + 'ColumnStore: ' + CASE WHEN @CurrentIsColumnStore = 1 THEN 'Yes' WHEN @CurrentIsColumnStore = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '

          SET @CurrentComment = @CurrentComment + 'PageCount: ' + ISNULL(CAST(@CurrentPageCount AS nvarchar),'N/A') + ', '

          SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + ISNULL(CAST(@CurrentFragmentationLevel AS nvarchar),'N/A')

        END

 

        IF @CurrentIndexID IS NOT NULL AND (@CurrentPageCount IS NOT NULL OR @CurrentFragmentationLevel IS NOT NULL)

        BEGIN

        SET @CurrentExtendedInfo = (SELECT *

                                    FROM (SELECT CAST(@CurrentPageCount AS nvarchar) AS [PageCount],

                                                 CAST(@CurrentFragmentationLevel AS nvarchar) AS Fragmentation

                                    ) ExtendedInfo FOR XML AUTO, ELEMENTS)

        END

 

        IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)

        BEGIN

          SET @CurrentCommandType13 = 'ALTER_INDEX'

 

          SET @CurrentCommand13 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand13 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand13 = @CurrentCommand13 + 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)

 

          IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE')

          BEGIN

            SET @CurrentCommand13 = @CurrentCommand13 + ' REBUILD'

            IF @CurrentIsPartition = 1 SET @CurrentCommand13 = @CurrentCommand13 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)

            SET @CurrentCommand13 = @CurrentCommand13 + ' WITH ('

            IF @SortInTempdb = 'Y' AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + 'SORT_IN_TEMPDB = ON'

            IF @SortInTempdb = 'N' AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + 'SORT_IN_TEMPDB = OFF'

            IF @CurrentIndexType IN(1,2,3,4) AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + ', '

            IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + 'ONLINE = ON'

            IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @WaitAtLowPriorityMaxDuration IS NOT NULL SET @CurrentCommand13 = @CurrentCommand13 + ' (WAIT_AT_LOW_PRIORITY (MAX_DURATION = ' + CAST(@WaitAtLowPriorityMaxDuration AS nvarchar) + ', ABORT_AFTER_WAIT = ' + UPPER(@WaitAtLowPriorityAbortAfterWait) + '))'

            IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + 'ONLINE = OFF'

            IF @CurrentMaxDOP IS NOT NULL SET @CurrentCommand13 = @CurrentCommand13 + ', MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)

            IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

            IF @PadIndex = 'Y' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', PAD_INDEX = ON'

            IF @PadIndex = 'N' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', PAD_INDEX = OFF'

            SET @CurrentCommand13 = @CurrentCommand13 + ')'

          END

 

          IF @CurrentAction IN('INDEX_REORGANIZE')

          BEGIN

            SET @CurrentCommand13 = @CurrentCommand13 + ' REORGANIZE'

            IF @CurrentIsPartition = 1 SET @CurrentCommand13 = @CurrentCommand13 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)

            SET @CurrentCommand13 = @CurrentCommand13 + ' WITH ('

            IF @LOBCompaction = 'Y' SET @CurrentCommand13 = @CurrentCommand13 + 'LOB_COMPACTION = ON'

            IF @LOBCompaction = 'N' SET @CurrentCommand13 = @CurrentCommand13 + 'LOB_COMPACTION = OFF'

            SET @CurrentCommand13 = @CurrentCommand13 + ')'

          END

 

          EXECUTE @CurrentCommandOutput13 = [DBO].[spCommandExecute] @Command = @CurrentCommand13, @CommandType = @CurrentCommandType13, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LogToTable = @LogToTable, @Execute = @Execute

          SET @Error = @@ERROR

          IF @Error <> 0 SET @CurrentCommandOutput13 = @Error

          IF @CurrentCommandOutput13 <> 0 SET @ReturnCode = @CurrentCommandOutput13

 

          IF @Delay > 0

          BEGIN

            SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')

            WAITFOR DELAY @CurrentDelay

          END

        END

 

        IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = 'Y' AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)

        BEGIN

          SET @CurrentCommandType14 = 'UPDATE_STATISTICS'

 

          SET @CurrentCommand14 = ''

          IF @LockTimeout IS NOT NULL SET @CurrentCommand14 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '

          SET @CurrentCommand14 = @CurrentCommand14 + 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentStatisticsName)

          IF @StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ' WITH'

          IF @StatisticsSample = 100 SET @CurrentCommand14 = @CurrentCommand14 + ' FULLSCAN'

          IF @StatisticsSample IS NOT NULL AND @StatisticsSample <> 100 AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) SET @CurrentCommand14 = @CurrentCommand14 + ' SAMPLE ' + CAST(@StatisticsSample AS nvarchar) + ' PERCENT'

          IF @StatisticsResample = 'Y' OR (@CurrentIsMemoryOptimized = 1 AND (@StatisticsSample <> 100 OR @StatisticsSample IS NULL)) SET @CurrentCommand14 = @CurrentCommand14 + ' RESAMPLE'

          IF (@StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentIsMemoryOptimized = 1) AND @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ','

          IF @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ' NORECOMPUTE'

 

          EXECUTE @CurrentCommandOutput14 = [DBO].[spCommandExecute] @Command = @CurrentCommand14, @CommandType = @CurrentCommandType14, @Mode = 2, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @StatisticsName = @CurrentStatisticsName, @LogToTable = @LogToTable, @Execute = @Execute

          SET @Error = @@ERROR

          IF @Error <> 0 SET @CurrentCommandOutput14 = @Error

          IF @CurrentCommandOutput14 <> 0 SET @ReturnCode = @CurrentCommandOutput14

        END

 

        NoAction:

 

        -- Update that the index is completed

        UPDATE @tmpIndexesStatistics

        SET Completed = 1

        WHERE Selected = 1

        AND Completed = 0

        AND ID = @CurrentIxID

 

        -- Clear variables

        SET @CurrentCommand02 = NULL

        SET @CurrentCommand03 = NULL

        SET @CurrentCommand04 = NULL

        SET @CurrentCommand05 = NULL

        SET @CurrentCommand06 = NULL

        SET @CurrentCommand07 = NULL

        SET @CurrentCommand08 = NULL

        SET @CurrentCommand09 = NULL

        SET @CurrentCommand10 = NULL

        SET @CurrentCommand11 = NULL

        SET @CurrentCommand12 = NULL

        SET @CurrentCommand13 = NULL

        SET @CurrentCommand14 = NULL

 

        SET @CurrentCommandOutput13 = NULL

        SET @CurrentCommandOutput14 = NULL

 

        SET @CurrentCommandType13 = NULL

        SET @CurrentCommandType14 = NULL

 

        SET @CurrentIxID = NULL

        SET @CurrentSchemaID = NULL

        SET @CurrentSchemaName = NULL

        SET @CurrentObjectID = NULL

        SET @CurrentObjectName = NULL

        SET @CurrentObjectType = NULL

        SET @CurrentIsMemoryOptimized = NULL

        SET @CurrentIndexID = NULL

        SET @CurrentIndexName = NULL

        SET @CurrentIndexType = NULL

        SET @CurrentStatisticsID = NULL

        SET @CurrentStatisticsName = NULL

        SET @CurrentPartitionID = NULL

        SET @CurrentPartitionNumber = NULL

        SET @CurrentPartitionCount = NULL

        SET @CurrentIsPartition = NULL

        SET @CurrentIndexExists = NULL

        SET @CurrentStatisticsExists = NULL

        SET @CurrentIsImageText = NULL

        SET @CurrentIsNewLOB = NULL

        SET @CurrentIsFileStream = NULL

        SET @CurrentIsColumnStore = NULL

        SET @CurrentAllowPageLocks = NULL

        SET @CurrentNoRecompute = NULL

        SET @CurrentStatisticsModified = NULL

        SET @CurrentOnReadOnlyFileGroup = NULL

        SET @CurrentFragmentationLevel = NULL

        SET @CurrentPageCount = NULL

        SET @CurrentFragmentationGroup = NULL

        SET @CurrentAction = NULL

        SET @CurrentMaxDOP = NULL

        SET @CurrentUpdateStatistics = NULL

        SET @CurrentComment = NULL

        SET @CurrentExtendedInfo = NULL

 

        DELETE FROM @CurrentActionsAllowed

 

      END

 

    END

 

    -- Update that the database is completed

    UPDATE @tmpDatabases

    SET Completed = 1

    WHERE Selected = 1

    AND Completed = 0

    AND ID = @CurrentDBID

 

    -- Clear variables

    SET @CurrentDBID = NULL

    SET @CurrentDatabaseID = NULL

    SET @CurrentDatabaseName = NULL

    SET @CurrentIsDatabaseAccessible = NULL

    SET @CurrentAvailabilityGroup = NULL

    SET @CurrentAvailabilityGroupRole = NULL

    SET @CurrentDatabaseMirroringRole = NULL

 

    SET @CurrentCommand01 = NULL

 

    DELETE FROM @tmpIndexesStatistics

 

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Log completing information                                                                 //--

  ----------------------------------------------------------------------------------------------------

 

  Logging:

  SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)

  SET @EndMessage = REPLACE(@EndMessage,'%','%%')

  RAISERROR(@EndMessage,10,1) WITH NOWAIT

 

  IF @ReturnCode <> 0

  BEGIN

    RETURN @ReturnCode

  END

 

  ----------------------------------------------------------------------------------------------------

 

END

 

go

 

USE [master]

GO

/****** Object:  StoredProcedure [DBO].[spCommandExecute]    Script Date: 13/02/2017 12:41:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

if OBJECT_ID('[DBO].[spCommandExecute]') > 0

                drop proc [DBO].[spCommandExecute]

go

CREATE PROCEDURE [DBO].[spCommandExecute]

 

@Command nvarchar(max),

@CommandType nvarchar(max),

@Mode int,

@Comment nvarchar(max) = NULL,

@DatabaseName nvarchar(max) = NULL,

@SchemaName nvarchar(max) = NULL,

@ObjectName nvarchar(max) = NULL,

@ObjectType nvarchar(max) = NULL,

@IndexName nvarchar(max) = NULL,

@IndexType int = NULL,

@StatisticsName nvarchar(max) = NULL,

@PartitionNumber int = NULL,

@ExtendedInfo xml = NULL,

@LogToTable nvarchar(max),

@Execute nvarchar(max)

 

AS

 

BEGIN

 

-- Source: https://ola.hallengren.com

 

  SET NOCOUNT ON

 

  DECLARE @StartMessage nvarchar(max)

  DECLARE @EndMessage nvarchar(max)

  DECLARE @ErrorMessage nvarchar(max)

  DECLARE @ErrorMessageOriginal nvarchar(max)

 

  DECLARE @StartTime datetime

  DECLARE @EndTime datetime

 

  DECLARE @StartTimeSec datetime

  DECLARE @EndTimeSec datetime

 

  DECLARE @ID int

 

  DECLARE @Error int

  DECLARE @ReturnCode int

 

  SET @Error = 0

  SET @ReturnCode = 0

 

  ----------------------------------------------------------------------------------------------------

  --// Check core requirements                                                                    //--

  ----------------------------------------------------------------------------------------------------

 

  IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')

  BEGIN

    SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO ReturnCode

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Check input parameters                                                                     //--

  ----------------------------------------------------------------------------------------------------

 

  IF @Command IS NULL OR @Command = ''

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Mode NOT IN(1,2) OR @Mode IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Execute NOT IN('Y','N') OR @Execute IS NULL

  BEGIN

    SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

  END

 

  IF @Error <> 0

  BEGIN

    SET @ReturnCode = @Error

    GOTO ReturnCode

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Log initial information                                                                    //--

  ----------------------------------------------------------------------------------------------------

 

  SET @StartTime = GETDATE()

  SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)

 

  SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)

  SET @StartMessage = @StartMessage + 'Command: ' + @Command

  IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment

  SET @StartMessage = REPLACE(@StartMessage,'%','%%')

  RAISERROR(@StartMessage,10,1) WITH NOWAIT

 

  IF @LogToTable = 'Y'

  BEGIN

    INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)

    VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)

  END

 

  SET @ID = SCOPE_IDENTITY()

 

  ----------------------------------------------------------------------------------------------------

  --// Execute command                                                                            //--

  ----------------------------------------------------------------------------------------------------

 

  IF @Mode = 1 AND @Execute = 'Y'

  BEGIN

    EXECUTE(@Command)

    SET @Error = @@ERROR

    SET @ReturnCode = @Error

  END

 

  IF @Mode = 2 AND @Execute = 'Y'

  BEGIN

    BEGIN TRY

      EXECUTE(@Command)

    END TRY

    BEGIN CATCH

      SET @Error = ERROR_NUMBER()

      SET @ReturnCode = @Error

      SET @ErrorMessageOriginal = ERROR_MESSAGE()

      SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')

      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    END CATCH

  END

 

  ----------------------------------------------------------------------------------------------------

  --// Log completing information                                                                 //--

  ----------------------------------------------------------------------------------------------------

 

  SET @EndTime = GETDATE()

  SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)

 

  SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)

  SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)

  SET @EndMessage = @EndMessage + 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '

  SET @EndMessage = REPLACE(@EndMessage,'%','%%')

  RAISERROR(@EndMessage,10,1) WITH NOWAIT

 

  IF @LogToTable = 'Y'

  BEGIN

    UPDATE dbo.CommandLog

    SET EndTime = @EndTime,

        ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,

        ErrorMessage = @ErrorMessageOriginal

    WHERE ID = @ID

  END

 

  ReturnCode:

  IF @ReturnCode <> 0

  BEGIN

    RETURN @ReturnCode

  END

 

  ----------------------------------------------------------------------------------------------------

 

END

 

 

 

 

2º - Prepare the Windows Schedule Task to execute it

-------------------------------------------------------------------

 

Transaction:

exec DBO.spMaintenance @Operation = 'BackupDB'

 

exec DBO.spMaintenance @Operation = 'ReIndex'

 

exec DBO.spMaintenance @Operation = 'CycleLog'

 

SQL Backup:

--------------------------

Command line: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"

TSQL: -UusrDBO -PPassword -Q "exec DBO.spMaintenance @Operation='BackupDB'"

 

 

SQL Maintenance:

--------------------------

Command line: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"

TSQL: -UusrDBO -PPassword -Q "exec DBO.spMaintenance @Operation='BackupDB'"

 

Command line: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"

TSQL: -UusrDBO -PPassword -Q "exec DBO.spMaintenance @Operation='BackupDB'"

 


Crie um site com

  • Totalmente GRÁTIS
  • Centenas de templates
  • Todo em português

Este site foi criado com Webnode. Crie um grátis para você também!