Restoring a SQL Server database marked as suspect

Have you encountered this error “spid13 Bypassing recovery for database ‘yourdb’ because it is marked SUSPECT”. One of my friends got this error on his SQL Server 2000 database. This error usually occurs when SQL Server is not able to access a database

If you plan to recover a corrupt database on your own, here are some simple solutions that may work:

1. Run this command

UPDATE master.dbo.sysdatabases
SET status=status & ~256
WHERE name= 'yourdbname'

2. If that does not solve the problem, here’s a way to do it in emergency mode (untested)

UPDATE master.dbo.sysdatabases
SET status = status  -32768
where name = 'yourdbname'
If you know of any other way, drop in a comment. Thanks!
at

3 comments:

  1. Jayaprakash RAugust 3, 2010 at 6:58 AM

    0. Convert the database from Suspect mode to emergency mode.
    1. Rebuild the Transaction Log using dbcc_rebuildlog command. This will create the transaction log in new location.
    2. Detach the database.
    3. Delete the old transation file.
    4. Copy the new transaction file to the location where the mdf file exists.
    5. Attach the database, specify the mdf file location.
    6. You can see the database in normal mode and ecovered from Suspect mode.

    I have tried this and working for me. Hope this helps you too.

    Regards,
    JP

    ReplyDelete
  2. Mark WilliumJune 14, 2011 at 2:53 AM

    Run given below query in query analyzer

    "ALTER DATABASE Database_name SET EMERGENCY
    DBCC checkdb(’Database_name’)
    ALTER DATABASE Database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB (’Database_name’, REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE Database_name SET MULTI_USER"

    This query is not 100% safe you might have loss some data after executing this query. My suggestion for you to try any authorized recovery or repair software for SQL.

    ReplyDelete
  3. UnknownMay 20, 2014 at 2:38 AM

    1. Restore the database backup file.

    2. If no backup is available, then run the following commands

    ALTER DATABASE Databasename SET EMERGENCY
    DBCC CHECKDB 'Databasename'
    ALTER DATABASE Databasename SET SINGLE USER with ROLLBACK IMMEDIATE
    DBCC CHECKDB 'Databasename, REPAIR_ALLOW_DATA_LOSS'
    ALTER DATABASE Databasename SET MULTIUSER

    Use third party tool which helps you to easily repair and recover database from suspect mode. http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html

    ReplyDelete