In one of our project modules I am currently working on there is a requirement to backup and restore the databases as and when required. They are doing it programmatically from the application side. Everything went well with the backup. But when they are restoring we need to validate whether the .BAK file that they are passing is a valid one or not. How to validate this? In SQL Server we have an option while restoring the database RESTORE VERIFY ONLY. It will not restore the backup but it will check whether the backup is complete and readable. I used the following script to do the same.
declare @verifystatement nvarchar(250)
declare @backupdevice nvarchar(250)
declare @err int
set @backupdevice =‘C:\Temp\TestDB.bak’ –Select the .bak file path here
set @verifystatement =‘RESTORE VERIFYONLY FROM DISK = ”’+ @backupdevice + ””
exec sp_executesql @verifystatement
SELECT @err = @@error
if @err <> 0
PRINT ‘Database backup is corrupt’
PRINT ‘Database backup is valid’
From the application side they can put this in a function and call this to identify whether the database backup file is corrupt or not. Based on the result from this function they can take whether to backup or throw an exception.
For more details please refer to http://msdn.microsoft.com/en-us/library/aa238412(SQL.80).aspx