Click here to Skip to main content
Click here to Skip to main content

Tagged as

Verify SQL Server Backup

, 21 Dec 2012 BSD
Rate this:
Please Sign up or sign in to vote.
If you wanted to check the status of all your database backup then this script will do the task.

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

History

None so far.

License

This article, along with any associated source code and files, is licensed under The BSD License

Share

About the Author

mparvez
Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0
 
An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.
 
Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.
 
Certification
 
70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker
 
Web :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez
Follow on   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 21 Dec 2012
Article Copyright 2012 by mparvez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid