Help with improving query

by JayM   Last Updated January 12, 2018 16:06 PM

Working on evaluating backup files with correction restoration path. Borrowed this query and made some tweaks.. wondering how could I make it even faster. like using maxdop or using better resource abundant server. any help will be helpful.

the script below...

----------------------------------------------------------------------------
-- Variable
----------------------------------------------------------------------------
DECLARE @Count INT
DECLARE @query NVARCHAR(2000)
DECLARE @current INT
DECLARE @name VARCHAR(2000)
DECLARE @tempBackupCheck TABLE
  (
     intid              INT IDENTITY(1, 1) PRIMARY KEY,
     NAME               NVARCHAR(2000),
     backup_start_date  DATETIME,
     backup_finish_date DATETIME,
     backup_size_mb     VARCHAR(20),
     backup_type        VARCHAR (50),
     error_msg          VARCHAR(2000),
     database_name      VARCHAR(2000),
     [status]           BIT
  )

----------------------------------------------------------------------------
-- 1. Select Backup
----------------------------------------------------------------------------
INSERT INTO @tempBackupCheck
            (NAME,
            database_name,
             backup_start_date,
             backup_finish_date,
             backup_size_mb,
             backup_type)
SELECT physical_device_name,
       database_name,
       backup_start_date,
       backup_finish_date,
       CONVERT(VARCHAR, Cast(backup_size / 1024 / 1024 AS MONEY), 10),
       CASE [type]
         WHEN 'D' THEN 'Full Backups'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Transaction Log'
         WHEN 'V' THEN 'Verifyonly'
       END AS rhType
FROM   msdb.dbo.backupset b
       JOIN msdb.dbo.backupmediafamily m
         ON b.media_set_id = m.media_set_id
WHERE  database_name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                              'DBA_Archive', 'ReportServer',
                              'ReportServerTempDB' )
       --AND   (backup_finish_date > DATEADD(hh, - 48, GETDATE())) 
       AND ( backup_finish_date > Dateadd(month, -1, Getdate()) )
ORDER  BY backup_finish_date DESC

----------------------------------------------------------------------------
-- 2. Verify
----------------------------------------------------------------------------
SELECT @Count = Count(intid)
FROM   @tempBackupCheck

IF ( ( @Count IS NOT NULL )
     AND ( @Count > 0 ) )
  BEGIN
      SET @current=1

      WHILE ( @current <= @Count )
        BEGIN
            SELECT @name = NAME
            FROM   @tempBackupCheck
            WHERE  intid = @current

            SET @query='RESTORE VERIFYONLY FROM DISK= ''' + @name
                       + ''' WITH CHECKSUM'

            BEGIN try
                PRINT @query;

                EXEC Sp_executesql
                  @query

                -- Update Staus
                UPDATE @tempBackupCheck
                SET    [status] = 1
                WHERE  intid = @current
            END try

            BEGIN catch
                -- Update Error Message and Staus
                UPDATE @tempBackupCheck
                SET    error_msg = Error_message(),
                       [status] = 0
                WHERE  intid = @current
            END catch

            SET @current=@current + 1
        END
  END

----------------------------------------------------------------------------
-- Show Output
----------------------------------------------------------------------------
SELECT *
FROM   @tempBackupCheck

--Analysis 1
INSERT INTO @tempBackupCheck
            (NAME,
             database_name,
             backup_start_date,
             backup_finish_date,
             backup_size_mb,
             backup_type)
SELECT physical_device_name,
       database_name,
       backup_start_date,
       backup_finish_date,
       CONVERT(VARCHAR, Cast(backup_size / 1024 / 1024 AS MONEY), 10),
       CASE [type]
         WHEN 'D' THEN 'Full Backups'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Transaction Log'
         WHEN 'V' THEN 'Verifyonly'
       END AS rhType
FROM   msdb.dbo.backupset b
       JOIN msdb.dbo.backupmediafamily m
         ON b.media_set_id = m.media_set_id
WHERE  database_name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
       --AND   (backup_finish_date > DATEADD(hh, - 48, GETDATE()))  
       AND ( backup_finish_date > Dateadd(month, -1, Getdate()) )
ORDER  BY backup_finish_date DESC


Related Questions



MongoDB Backup & purge

Updated May 18, 2015 23:02 PM