Backup verification prior to restore

rajk007rajk007 Posts: 3
edited July 27, 2006 10:05PM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    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 = &#91;e:\backups\pubs.sqb&#93; 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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.