Verify SQL Server Backup

• Verify backup script - 1 KB

Introduction

Although not required, verifying a backup is a useful practice. Verifying a backup checks that the backup is intact physically, to ensure that all the files in the backup are readable and can be restored, and that you can restore your backup in the event you need to use it. It is important to understand that verifying a backup does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup.

Background

If you wanted to check the status of all your database backup then this script will do the task. You could easily modify it for your particular need such as check the last 48 hours backup.

What you need

This script was tested in SQL Server 2008.

Main script

----------------------------------------------------------------------------
-- 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),
    
[status] bit    

)

----------------------------------------------------------------------------
-- 1. Select Backup
----------------------------------------------------------------------------

INSERT INTO @tempBackupCheck(name,backup_start_date,backup_finish_date,backup_size_mb,backup_type)
SELECT physical_device_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()))  
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

After executing the script

Analysis script

INSERT INTO @tempBackupCheck(name,backup_start_date,backup_finish_date,backup_size_mb,backup_type)
SELECT physical_device_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()))  
ORDER BY backup_finish_date DESC

This will retrieve physical backup file name for verifying and insert in a table for further processing. If you want to check only last 48 hours backup, include:

AND   (backup_finish_date > DATEADD(hh, - 48, GETDATE()))

Analysis script

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

From the table we are checking each file by loop. We execute the query and update its status. If no error found, the status is 1 otherwise it is 0. With a little bit more effort you can add mail generate if any error is found.

Conclusion

In SQL Server 2005 and later versions, the checks performed by RESTORE VERIFYONLY include:

• That the backup set is complete and all volumes are readable.
• Page ID (as if it were about to write the data).
• Checksum (if present on the media).
• Checking for sufficient space on destination devices.

Restoring a database does not guarantee that it can be recovered. Furthermore, a database recovered from a verified backup could have a problem with its data. This is because verifying a backup does not verify whether the structure of the data contained within the backup set is correct. For example, although the backup set may have been written correctly, a database integrity problem could exist within the database files that would comprise the backup set. However, if a backup was created with backup checksums, a backup that verifies successfully has a good chance of being reliable.

References

• Verifying backups: http://msdn.microsoft.com/en-us/library/ms189587(v=sql.105).aspx

History

None so far.