Compression Levels
cramossan
Posts: 3
I am currently trying to understand the benefits of using compression leve 2 or 1 on my systems. Previously I had used compression Level 3, which creates the smallest backup file possible, but it is too CPU intensive during normal everyday operations and thus affects the Server Applications. Anyway, my question is: IS LEVEL 1 safe?
I ask this because on the help file it says (and I quote)
" Part of the speed in compression level 1 is due to the fact that it does not generate checksums for its compressed data. Thus, if the backup file is corrupted (intentionally or unintentionally), a buffer overflow may occur. You should store the backup file in a secure location to prevent the backup file from being intentionally tampered with to cause a buffer overflow. "
Can somebody please explain this statement more profoundly?
my worry is that I am currently making an auto-backup every three hours
and I prefer not making backups of a partially corrupted database or is there any way SQL-Backup can prevent this?
Thank you.
I ask this because on the help file it says (and I quote)
" Part of the speed in compression level 1 is due to the fact that it does not generate checksums for its compressed data. Thus, if the backup file is corrupted (intentionally or unintentionally), a buffer overflow may occur. You should store the backup file in a secure location to prevent the backup file from being intentionally tampered with to cause a buffer overflow. "
Can somebody please explain this statement more profoundly?
my worry is that I am currently making an auto-backup every three hours
and I prefer not making backups of a partially corrupted database or is there any way SQL-Backup can prevent this?
Thank you.
Comments
Level 1 compression does away with this. This means that it does not store a checksum, and will just uncompress data without verification. If the backup file is corrupted, it will not be able to detect this corruption. Eventually, the restore will fail.
Even if using compression levels 2 and 3, a corrupted file is still a corrupted file. It will still fail to be restored. It's just that SQL Backup will be able to detect this corruption in a safe manner.
With regards to corrupted databases, SQL Server 2000 cannot detect this during a backup. However, SQL Server 2005 is able to do so, and either continue or abort the backup. This feature will be added to SQL Backup in a future version.
You might want to use DBCC CHECKDB periodically to check and repair any database inconsistencies.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
I have an additional question regarding this subject:
Is there any way I can integrate DBCC checkdb within a batch script in order to capture possible data corruption prior to generating a backup set?
I have a fundamental problem with electric power in my area and even having UPS have not provided a failsafe method to prevent data corruption
In this instance, do you think it would be better to use the extended stored procedure for auto-backups or a batch file?
Thanks.
The easiest way to run regular integrity checks on a database would be to set up a maintenance plan on the server. Using the maintenance plan wizard, you can check the integrity of some or all of your databases, reorganize index pages, and more.
SQL Backup does not integrate with maintenance plans. For regular scheduled backups, you can set up a SQL Agent job using the SQL Backup stored procedure or a batch job on Windows Scheduler using sqlbackupc.exe.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8