Feature Request - Inline integrity check

EdVassieEdVassie Posts: 15
edited April 28, 2008 4:53AM in SQL Backup Previous Versions
Description
Include the processing normally done by a DBCC CHECKDB inline with running a backup.
Most of the time required to do a CHECKDB is the time needed to read the DB files. Likewise most of the time needed to do a backup is the time needed to read the DB files.
The backup should have an option to read the DB files once and do perform consistancy checking before writing the backup.

Justification:
Finding a window to do a CHECKDB and a backup is not always easy. Being able to run one process that did both functions would save a lot of time.

Notes:
This type of functionality has already been released by BMC and CA for their mainframe DB2 products, so there may be patent issues to beware of.
Experience with the DB2 products is that doing an inline consistance check only added about 5% to the backup execution time. Most DB2 sites now use this functionality for all their backups due to the time saved.

Comments

  • This sounds like an excellent idea as well, but I also have a feeling it requires Microsoft to make it possible as RedGate just hooks into their backup API. I would add a feedback on connect. Link it if you do and I and others will vote it it.

    http://connect.microsoft.com/SQLServer/Feedback
  • peteypetey Posts: 2,358 New member
    If you are using SQL Server 2005, consider using the CHECKSUM option for backups. While it does not perform as extensive a check as DBCC CHECKDB, it does at least verify each pages' checksum.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • alecl,

    When BMC and CA did this for DB2, they did it on their own. There was no vendor API to help.

    My understanding from when I worked at BMC is they worked out what a valid page should look like, and highlighted any inconsistencies. Different pages (data, index, space map, etc) each have their own formats. Where relevant, pointers to next/previous pages (eg for spacemaps, etc) were checked to ensure the target page was of the correct type. CA must have done something similar for their product but which did not violate the BMC patents, as these 2 companies were and still are very serious rivals.

    The BMC product had various levels of integrity check the DBA could ask it to do. The most expensive checked that all index RIDS pointed to the right data, and that all off-page pointers went to whatever was valid.

    Most installations settled on the option do do page consistency and spacemap (etc) chain checks. This was the option that added only about 5% to elapsed time. The little feedback I had about the more complex options was they were shelfware.

    After about 10 years, IBM as DB2 vendor also thought that inline consistency checks were a good idea and added it to their own backup product.

    The moral of all this is Red-Gate could add inline consistency checks to SQL Backup without any additional help from Microsoft. However, I have posted this request on Connect so vote for it if you want: https://connect.microsoft.com/SQLServer ... kID=340720
Sign In or Register to comment.