Backup verification prior to restore

rajk007

Is it possible to verify the backup prior to restore to ensure the backup copy stored in a tape drive is "not corrupted"?

Thanks.

Raj

petey

Using SQL Server 2000, there is a RESTORE VERIFYONLY command, but this only checks if the data from the backup media can be read. It does not perform any checks on the integrity of the data itself.

From SQL Server 2000 Books Online:

Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes.

Using SQL Server 2005, there is an option to generate checksums for the database pages during a backup. If the checksum option is used, then during the RESTORE VERIFYONLY process, SQL Server will perform data integrity checks using the checksums.

On another note, are you using SQL Backup directly with tape devices?

rajk007

I am using SQL Server 2000. I just downloaded the 4.1 trial version and testing a few things prior to buying it.

In SQL Server 2000, Does the SQL Backup 4.1 GUI have the RESTORE VERIFYONLY command ?

I have not tried the backing up the data directly to tape devices. So far I have been creating the backup in the HD. What is the best practice with SQL Backup 4.1?

Any advice is appreciated.

Raj

petey

The GUI does not have a RESTORE VERIFYONLY ... function. Use the extended stored procedure to run that function e.g. in Query Analyzer, run the following:

exec master..sqlbackup '-sql "RESTORE VERIFYONLY FROM DISK = [<file name>]" '

It's not possible to backup to tape devices directly using SQL Backup. I asked the question as you mentioned tape drives in your initial post.

bradrman

Peter Yeoh,


The extended SP does not allow you to specify the WITH CHECKSUM options like it does for native sql server backups/restore. So if you specified the the WITH CHECKSUM option on the backup how would you veryify the checksums with out a full restore using the WITH CHECKSUM option...which _is_ supported by SQL Backup ?


Brad S

petey

According to the following docs:

RESTORE Arguments (Transact-SQL) (http://msdn2.microsoft.com/en-us/library/ms178615.aspx)
Detecting and Coping with Media Errors (http://msdn2.microsoft.com/en-us/library/ms189055.aspx)

If backup checksums are present on the backup media, by default, both the RESTORE and RESTORE VERIFYONLY operations verify the backup checksums and page checksums.

Thus, the verification of the checksum is performed automatically if they are present. In the next release, we'll allow the user to modify the default behaviour by supporting the CHECKSUM andn NO_CHECKSUM options to RESTORE VERIFYONLY.

bradrman

Peter Yeoh,


So what you are saying is that by using Red-Gate's SQL Backup extended stored procedure and passing the 'WITH CHECKSUM' that it performs a native Sql Server CHECKSUM verification? And then SQL Backup compresses the data?

petey

So what you are saying is that by using Red-Gate's SQL Backup extended stored procedure and passing the 'WITH CHECKSUM' that it performs a native Sql Server CHECKSUM verification? And then SQL Backup compresses the data?

Not really. When you issue a backup command to SQL Backup, SQL Backup in turns issue a backup command to SQL Server to perform the backup. Options that are relevant to SQL Server are passed along. So if you issued a WITH CHECKSUM command to SQL Backup, SQL Backup will use this option in the backup command sent to SQL Server.

E.g. command to SQL Backup

sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [e:\backups\pubs.sqb] WITH ERASEFILES = 2, CHECKSUM" '

SQL Backup issues the following to SQL Server:

BACKUP DATABASE pubs TO VIRTUAL DEVICE = '...' WITH CHECKSUM

So what you get (at least in SQL Server 2005) will be a backup file with embedded checksums. When you now perform a RESTORE VERIFYONLY, SQL Backup uncompresses the backup data, and passes this along to SQL Server for verification. According to the docs, SQL Server will detect the presence of the checksums, and adjust the verification accordingly.

bradrman

Please forgive my ignorance on this matter, but if SQL Backup simply passes a 'Normal' backup command to SQL Server, How is it that it is much much faster than the native backup? Also, when/how is it compressed? Also If Sql Backup uncompresses the data and then issues a 'Normal' Sql Server restore, how come the restore is also much much faster?

petey

The difference lies in the VIRTUAL DEVICE option. Instead of backing up to disk, SQL Server passes the backup data to the virtual device created by SQL Backup. SQL Backup then compresses/encrypts the data, then writes them out to disk.