Verify SQL Server Backup
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
- Verifying backups: http://msdn.microsoft.com/en-us/library/ms189587(v=sql.105).aspx
History
None so far.