25/10/2016 Comments(0)

    Restore SQL Server 2005 Suspect Database

    If you find your database in Suspect mode, then please keep your nerve strong. Just proceed step by step what I am written below. I think you will get out of this trouble. SQL Server 2005 introduced a new DB Status called Emergency. This mode can change the DB from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode. The steps are… After executing the script given below, you will get back your database in operational mode. Actually I have tried with two of my existing live systems and found no data loss.

    Note: Obviously there are two more options available. Run REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transitionally consistent state. Here are a few things to bear in mind about emergency mode repair: it’s a one-way operation. Anything it does cannot be rolled back or undone. If this worries you (if someone ever got into that state, then surely don’t have the healthy concern about data that they should have in the first place) then make a copy of the damaged database before you run emergency mode repair.
    As it’s a one-way operation, you cannot wrap it in an explicit user-transaction.
    It’s the only repair option available in emergency mode – if you try to use REPAIR_REBUILD, then it won’t work.

    EXEC sp_resetstatus 'yourDBname';
    ALTER DATABASE yourDBname SET EMERGENCY
    DBCC checkdb('yourDBname')
    ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE yourDBname SET MULTI_USER
    
    

    You will be happy that your database is recovered 🙂