April 06, 2010

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!


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

2 Responses to "Restoring a SQL Server database marked as suspect"
  1. Jayaprakash R said...
    August 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

  2. Mark Willium said...
    June 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.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions