locked
Can I take the backup of it? RRS feed

  • Question

  • My databse is looking in suspect mode?

    Can I take the backup of  it?What should I do?


    "SQLSERVER DBA" "INDIA"
    Tuesday, August 17, 2010 5:51 AM

Answers

  • You can take BACKUP LOG file depends on the damage...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 17, 2010 6:49 AM
  • The first step is always, as URI writes to take a backup of the log file. You can force backup if needed to continue past errors. look at CONTINUE_AFTER_ERROR in http://msdn.microsoft.com/en-us/library/ms186865.aspx

    Next step is to get it back in working order. Either you have a backup, and the database is in full recovery mode, then you can restore the database and append all the log files until the last newly backed up log file. If this is not an option, you can repair the database in emergency repair mode. But first run DBCC CHECKDB ([dbname]) WITH ALL_ERRORMSGS, NO_INFOMSGS.

    Always let the DBCC CHECKDB finish and tell you the error. It's been known to take up to 30 hours. Here you have to choose recoverypath, i e restore backup and apply logs, or repair the database. Please respond back with what the DBCC CHECKDB result is so we can advice further!

    When you've recovered any way you can, don't stop there. You need to find the root cause, otherwise you'll most probably be hit with corruption again. Memory and I/O system failures are things you should suspect. Check Errorlog for:
    I/O Errors
    823 Hard I/O Error - SQL requests a read from OS, and OS says NO.
    824 Soft I/O Error - SQL requests a read and determines it's corrupt. A Tornpage or checksum error
    825 Hard I/O Error - OS reads multiple times, and finally gets the data. SCAN Log for 825, or you'll miss it.

    Check OS Eventlogs for disk subsystem errors.

    And dont forget to run DBCC CHECKDB again after you've recovered


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Tuesday, August 17, 2010 10:41 AM

All replies

  • You can take BACKUP LOG file depends on the damage...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 17, 2010 6:49 AM
  • The first step is always, as URI writes to take a backup of the log file. You can force backup if needed to continue past errors. look at CONTINUE_AFTER_ERROR in http://msdn.microsoft.com/en-us/library/ms186865.aspx

    Next step is to get it back in working order. Either you have a backup, and the database is in full recovery mode, then you can restore the database and append all the log files until the last newly backed up log file. If this is not an option, you can repair the database in emergency repair mode. But first run DBCC CHECKDB ([dbname]) WITH ALL_ERRORMSGS, NO_INFOMSGS.

    Always let the DBCC CHECKDB finish and tell you the error. It's been known to take up to 30 hours. Here you have to choose recoverypath, i e restore backup and apply logs, or repair the database. Please respond back with what the DBCC CHECKDB result is so we can advice further!

    When you've recovered any way you can, don't stop there. You need to find the root cause, otherwise you'll most probably be hit with corruption again. Memory and I/O system failures are things you should suspect. Check Errorlog for:
    I/O Errors
    823 Hard I/O Error - SQL requests a read from OS, and OS says NO.
    824 Soft I/O Error - SQL requests a read and determines it's corrupt. A Tornpage or checksum error
    825 Hard I/O Error - OS reads multiple times, and finally gets the data. SCAN Log for 825, or you'll miss it.

    Check OS Eventlogs for disk subsystem errors.

    And dont forget to run DBCC CHECKDB again after you've recovered


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Tuesday, August 17, 2010 10:41 AM