SQL Server Performance

Does database backup process fail if the database to be backed up is already corrupt?

Discussion in 'ALL SQL SERVER QUESTIONS' started by bhushank21, Feb 8, 2012.

  1. bhushank21 New Member

    Hi all,

    Does database backup process fail if the database to be backed up is already corrupt?
    We are using SQL Server 2005.
    We wanted to know if this is detected at the backup itself rather than during restoring...
    Thanks and Regards,
    Bhushan Khadkikar
  2. davidfarr Member

    If you are performing a 'standard' SQL Server 2005 backup, with default options, and if the physical disk media is healthy, then the backup will usually not fail even if the database is 'corrupt'.
    There are many different consistency problems that can 'corrupt' a database. Some are very serious and some are less serious.
    If you look at all the checks performed by DBCC CHECKDB for example, the BACKUP command certainly does not check all those same things.
    Likewise, the RESTORE command does not check all those things either. It is possible to backup and restore a database that still has small consistency problems.

    There is a CHECKSUM option that you can specify in the BACKUP command syntax which will check for page checksum or torn pages, but does not check other things.
    If you specify the CHECKSUM option, and if a torn page is detected during the backup, then the backup will fail.
    Furthermore, a backup will fail if there is a serious i/o media problem; such as bad sectors on the disk that make parts of the data file or log file unreadable.

Share This Page