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!
About The Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and a new one recently at
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.
Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook
3 comments:
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
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.
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
Post a Comment