It’s one thing to be able to backup a database, it’s another thing to be able to restore it into an operational database in case of failure. We can run a simple TSQL to check the validity of a backup file. We can do something like this:
[sql] RESTORE VERIFYONLY FROM DISK = ‘C:\TestDB.bak'[/sql]
It’s easy, right? You don’t have to have some ninja skills to run that statement. Run the verification, and if SQL Server validates the backup, your job here is done. Yes to that, only if you can rely on VERIFYONLY.
Valid doesn’t mean you can restore it
The fact is you can’t rely on VERIFYONLY for real backup verification. It doesn’t guarantee that you can RESTORE the backup although it might report your database backup as VALID. If it reports validity, it’s only guaranteeing that it can READ the backup file, but not necessarily for restoration. “Validity” in this case is a test for whether the backup file is readable or not.
Let’s run an example.
For this example let’s borrow Paul Randal’s corrupt database backup. Let’s pick “Sample 2008 R2 Fatal Corruption Databases”. And that is because I am running a SQL Server 2008 R2 on my laptop.
Let’s verify the CorruptDemoFatalCorruption1.bak backup file and see if SQL Server validates it:
[sql]
RESTORE VERIFYONLY FROM DISK = ‘C:\BackupTest\CorruptDemoFatalCorruption1.bak’
[/sql]
This is what SQL Server found:
(For the sake of this example, let’s just ignore the fact that you should be monitoring the health of your databases.)
The best you could do to make sure that you can restore your backup is to actually restore it in a test server. I’m sure you’re going to have some fun doing that on hundred’s of databases.
Third-party backup and restore tool
This is where third-party backup and restore tools can easily help. Let’s take a look at the latest version of Red Gate’s SQL Backup Pro (v.7).
Since we’re talking about validity of a backup for restoration, let’s take a look particularly at one of the new features of SQL Backup Pro v.7 – Automated Backup Verification and Restore. I’ll provide a couple of links at the end of this post if you want to check the demo of the new capabilities of the application. Let’s zoom in to the verification of a backup.
Let’s restore the backup CorruptDemoFatalCorruption1.bak. To check for the data and log files needed for restoration, just do RESTORE FILELISTONLY first.
[sql]
RESTORE FILELISTONLY
FROM DISK = ‘C:\BackupTest\CorruptDemoFatalCorruption1.bak’
[/sql]
Then restore:
[sql]
RESTORE DATABASE CorruptDemoFatalCorruption1
FROM DISK = ‘C:\BackupTest\CorruptDemoFatalCorruption1.bak’
WITH MOVE ‘DemoFatalCorruption1’
TO ‘C:\BackupTest\RestoreTest\Corruption1.mdf’,
MOVE ‘DemoFatalCorruption1_log’
TO ‘C:\BackupTest\RestoreTest\Corruption1.ldf’
[/sql]
We should get a message similar to this:
Let’s cut to the chase and go straight to the verification process. Like I said, I’ll provide links at the end of this post if you want to check all the features of SQL Backup Pro v7.
For this demonstration, I’ve already created a backup of CorruptDemoFatalCorruption1 in advance using SQL Backup Pro. Suppose we’re not aware of the health status of the database (we know that it’s a corrupt database).
Now that we have the backup, the next thing we want to do is verify that backup and make sure that we can restore it. We can do the verification by creating a Schedule Restore Job and running it. At step 4 of 7 of the process, we specify verification options for the restored database as shown below:
When run, SQL Backup Pro v7 provides the Outcome status of the restore. In our case, we know that our database restore will fail, and SQL Backup Pro accurately tells us that:
Of course, you can check where the Database Consistency Check failed:
Grant Fritchey (Blog | @GFritchey) have produced the following resources to demo the new features of SQL Backup Pro v7:
SQL Backup Pro for the Accidental DBA (article)
Introduction To SQL Backup Pro v7 (video)
