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?
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
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 ?
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
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?
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.
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?
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
Comments
From SQL Server 2000 Books Online:
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?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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)
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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?
E.g. command to SQL Backup
SQL Backup issues the following to SQL Server:
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8