Compression Levels

cramossancramossan Posts: 3
edited November 26, 2005 2:12AM in SQL Backup Previous Versions
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.

Comments

  • peteypetey Posts: 2,358 New member
    In most compression schemes, a checksum is generated for an uncompressed block of data, so that when the compressed data is later uncompressed, the application can use the checksum to ensure that the data has been correctly uncompressed.

    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thank you for your prompt answer!.

    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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    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.
  • peteypetey Posts: 2,358 New member
    A SQL Server maintenance plans creates a job and job step. Add a SQL Backup job step after the maintenance plan job step.
    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.