RESTORE VERIFY ONLY


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

 begin

   PRINT ‘Database backup is corrupt’

 end

else

begin

  PRINT ‘Database backup is valid’

End

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

Advertisements
This entry was posted in Interesting Stuff. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s