0

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
5
  • What do you want to achieve ? There might be better ways – Kin Shah Jan 12 '18 at 15:31
  • Hey Kin, yeah I was trying to verify the backups whether all backups are still valid to restore. – JayM Jan 12 '18 at 16:02
  • 1
    The only way you can do that is to perform a restore. Running RESTORE VERIFYONLY does not tell you that a database is actually restorable. It checks that the backup is readable, but not the structure of the data contained. See docs.microsoft.com/en-us/sql/t-sql/statements/… – Nic Jan 12 '18 at 16:37
  • Is performance really an issue with this script? I can't believe it'll take long, except for the RESTORE VERIFYONLY. So if you want that check, it'll take the time it's currently taking. If you need to speed up, write an application that can run the queries parallel (assuming that is allowed, maybe it isn't). – HoneyBadger Jan 13 '18 at 23:21
  • @HoneyBadger I'll try that and some other options and update here.Thanks for inputs. – JayM Jan 15 '18 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.