How to Recover MSSQL from "Suspect Mode" / "Emergency Mode" / "Error 1813"

This article describes the course of actions you should take

  1. if your database somehow got "suspect mode" or "emergency mode";
  2. if you receive the error 1813 on attaching database

The History Behind 

I've been developing under SQL 7 for a while. Sometimes transaction log grew too large. My usual fix for that problem was:

  1. stop the server (on the development machine we do not need any logs), 
  2. delete transaction log
  3. start the server again
  4. after that the server was creating empty 1MB transaction log from scratch

I had been using this technique a lot of times under SQL 7, until sometime I have migrated to MSSQL 2000...

After my transaction log grew too large, I stopped the server, deleted the log file and started the server once again. However this time it did not worked: my database got "suspect". I was not able to read or write from the database. A week of work seemed to be lost... Ouch!...

Screenshot of 'pubs' database in suspect mode

I searched the newsgroups - a lot of people were facing the same problem and nobody knew the solution. All I found was the article: http://support.microsoft.com/support/kb/articles/q251/6/28.asp. I have tried that and revealed information in the article applies only to SQL 7 and does not to SQL 2000...

Did I solved the problem? Read on!

"Suspect Mode" --> "Emergency Mode"

Open Enterprise Manager,

  1. Right click onto your server node, - an SQL Server Properties dialog will popup,
  2. Enable "Allow modifications to be made directly to the system catalogs" option
    Screenshot of Enterprise Manager/SQL Server Properties/Allow modifications to be made directly to the system catalogs
  3. Now proceed to the sysdatabases table in the master database, locate your database row and put 32768 into its status column
    Screenshot of master/sysdatabases/32768 in 'status' column of 'pubs' database
  4. Restart SQL Server and refresh Enterprise Manager

Open Enterprise Manager - the database will be marked as "Emergency Mode"

Screenshot of 'pubs' database in emergency mode

These articles explain the trick behind suspect and emergency mode:
http://support.microsoft.com/support/kb/articles/Q165/9/18.ASP
http://www.swynk.com/friends/knight/unmarksuspect.asp
http://support.microsoft.com/support/kb/articles/Q180/5/00.asp

"Emergency Mode" --> "Normal Mode"

Now your database is in "emergency mode". Disregard Enterprise Manager does not show tables, views and procedures - use Query Analyzer's Object Browser instead.

"Emergency mode" means database is locked against any modifications, it is readonly forever. There is no way to bring "emergency" database back to normal state. All we can do is copy all data from it into new database.

  1. Create new database (pubz)
    Screenshot of new 'pubz' database next to old 'pubs' database in emergency mode
  2. Create new DTS package
  3. Use "Copy SQL Objects" task to transfer data from old database to new database (pubs->pubz)
    Screenshot of Copy SQL Server Obbjects DTS task
  4. Execute the DTS job & enjoy!

Note that there might be complex dependencies between database objects, so Copy SQL Objects task might not work properly. For example: view A is dependent on view Z; Copy SQL Objects task will try to create view A first, -- that will fail, because view Z does not exist yet.

"Error 1813" --> "Suspect Mode"

If you receive "Error 1813", this probably means that you are trying to attach database you have improperly detached, or haven't detached at all. Only successfully detached databases can be successfully attached back.

Screenshot of Error 1813

  1. Create new database using the same name and file location as database that is failing to attach
  2. Stop SQL Server
  3. Copy non-attachable data files over the new ones
  4. Delete log files
  5. Start SQL Server

Open Enterprise Manager - the database will be marked as "suspect"

Screenshot of 'pubs' database in suspect mode

Once your database is "suspect", use instructions outlined above...

Note that database will be marked "suspect" only if transaction log contained active sections. To illustrate the problem for 100%, run a few inserts against the database before deleting transaction log.

Comments

You just saved me countless

You just saved me countless hours of work! THANKS!

Good

Good

I am trying to recover

I am trying to recover stored procedures and table definitions from a development database. I don't care about the data.

I have a back-up that is nearly three years old; I was thinking of recovering scripts for the object definitions and then re-applying them to the backed-up database. But Query Analyzer's object browser does not show me the dates of object creation. How will I identify which objects have changed if I cannot view them in Enterprise Manager?

Or should I simply rename or drop the EMERGENCY MODE database and then rename my new database to the old name? (Can a database in EMERGENCY MODE be renamed?) I don't want to lose the EMERGENCY MODE database -- just in case a dependency prevented an object from being copied by my DTS package.

Hi there, Just wanted to

Hi there,

Just wanted to remark that the info on your page did lead to a resolution for the problem, but in this case your 'endloesung' would have meant rewriting and distributing software with hardcoded routines, etc.

the following SQL script will restore the '0' or normal state on the database

use master

update sysdatabases set status = 0
where name like '<databaseinrecmodename>'

Great information. You were

Great information. You were an immense help in solving my problem!

Good keep it up good its

Good keep it up good its given a perfect solution for me

Thanks so much for this. It

Thanks so much for this. It probably saved my job!!!

great work

for two days i've been researching this issue and your post is the best so far.. thx

Database Suspect

Great information

Thanks very much

it worked! thx a bundle!

it worked! thx a bundle!

Suspect mode

Dear ,
plz help me to recover a mdf data from suspect / emergency mode
I m using sql 2000 i dont hv any backup
i m not able to recollect data
so plz help me to same

Thanks & Regards ,
Vipin jha
vipin_jha123@yahoo.co.in

Answer the mail or write to

Answer the mail or write to andriy.gerasika@gerixsoft.com

it worked!

Thanks for this post!!! I followed it and worked very well!!

thanksssssssssssssss

thanksssssssssssssss

foreign keys?

Is there a way around the foreign key restriction problem you described at the end? If I could just make sql not check for foriegn keys while I do this, I think that would do it.

You can try "script database"

You can try "script database" task, or

use master
update sysdatabases set status = 0
where name like '<databaseinrecmodename>'

from one of the comments above

MSSQL "suspect Mode"

Hello everybody,

the information <recovering MSSQL from 'suspect Mode'>
helps us to restore a important database. So, we saved a lot of money.
2039 tables of 2400 were rescued with the complete data.
We want to say, thank your for it. Thank you very much.

In that case a donation is normal. Please tell us your bank account...

nice regards

Marc

Thanks :) As a donation, you

Thanks :) As a donation, you may purchase IndieVolume license from http://www.indievolume.com

recovering emergence mode

its not working for me

what exactly step is not

what exactly step is not working? Is it MSSQL 2000 or are you trying to recover >=MSSQL 2005?

DB in Emergence/Recovery mode

This is what the error im getting when i try to runn any query

Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 0 in database 'DBNAME'.

Connection Broken

Please help in this regard

Do you have transaction log?

Do you have transaction log? Try to delete it

data copy faild

Hi dear
when I create DTS and wanted to copy data from corrupted database to new one , i got the error that mean it can't copy data because
[Microsoft][ODBC SQL Server Driver][SQL Server]'iranet.net\r_rostamy' is not a valid name because it contains invalid characters... i used windows authentication ..and from remote desktop did it. I always use windows authentication and my database doesn't have SQL server pass.... would you help me with this issue please? I'm struggleing this problem for days..... :-(

thank you in advanced
Ramin

I guess it is because of dot

I guess it is because of dot "." in domain name: iranet.net

try using regular MSSQL authentication

thank you so much

I just wanted tom say thank you buddy...your solution was so useful be welllllllllllllllllllllllllllllllllllll :-*

more

sorry, i did it local (i mean not from remote desktop) but it got that error again....
thank you

thanks

Thanks .

Your methods saved my hours.

I don't know waht to say

Thank you very much...........
Very nice Site.

you're the best

you save my client 7 year old database, its very important for my client, it was with sql server 7.0, I almost lose hope and I pray that God will help me via other people, and that is YOU. You're the best, thank you very much. God Bless.

Recover Suspect Database

Awesome...!!!!
Really.

Thanks a lot for this

Thanks a lot for this blog

This is really really useful to me.......

Thank you,
URVISH SUTHAR

is there any method to get my previous db

i restore new db without backup. my all db has many sp's. i want get that sp. is there any method.??

thank you.
--
NadeekaDN

I have prepared an exe which

I have prepared an exe which will recover the database from suspect mode.
If anyone need that exe you can mail me on partap1@gmail.com

Suspect recovery

Dear All,

Just mail to me at "suspectrecovery@gmail.com, i'll mail utility to you all.

Deepak Kumar

Suspect Recovery QUERY.. its 100% WORK..

..............................................................................................................................
EXEC sp_resetstatus DATABASENAME
GO
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 MULTI_USER
GO
ALTER INDEX ALL ON[TableName]REBUILD
ALTER INDEX ALL ON[TableName]REBUILD
ALTER INDEX ALL ON[TableName]REBUILD
...............................................................................................................................

Suspect

Detach the database and attach again wil work for SQL 2005.

Sql-2000 database repaire

i would like to get some advise or help to recover sql2000 database its suspect mode

Re: Repair corrupt SQL server database

We successfully repaired and restored our damaged SQL server databases with the help a third party program: http://www.serversdatarecovery.com/sqldatabase.html

One of the best aspects that I liked in this tool is that it preserves the accuracy and integrity of the database after recovery.

SQL Database Repair is the

SQL Database Repair is the best SQL database repair software that comes with plethora of options to recover data from corrupt or damaged SQL databases. The software can repair/recover both the .MDF and .NDF SQL database files. Corruption of damage of the SQL database file can occur due to several reasons, including application malfunction, file header corruption, OS crash, virus infection etc. The smart scan engine of the software is equipped to carry out precise database recovery through non-destructive mechanisms.For more information click http://database-recovery.weebly.com

This method ok for 2008

This method can use sql server 2008??

Desirable and Reliable Solution to Recover the SQL Database

Recover MS SQL Server database MDF file, NDF files in easy way with the use of well established SQL Server database recovery software. This MDF recovery tool recover the deleted tables of the SQL database. Software repair and make accessible all the SQL database file which is in suspect or emergency mode. Visit for more...http://www.sqlrepairtool.org

Recover Corrupt SQL Server Database.

SQL database stores data in MDF format which sometimes may get corrupt due to Virus attack, invalid database file header, insufficient available space, corruption of media etc. In such situations the user can use the software SQL Recovery which can recovers the data from corrups MDF file. More information about this tool can be found at: http://www.sql.recoverydeletedfiles.com/

Trusted solution for elimination of SQL error1813

To resolve such type of typical issue you aware that backup which you taken at the last time is safe and corrupt free. If it is corrupted than no solution is left to eradicate this error. But using other method you can recover from it. Please visit here for abrupt recovery
<a href="http://www.sqlrecoverysoftware.net/blog/sql-error-1813.html">http://www.sqlrecoverysoftware.net/blog/sql-error-1813.html</a>

Recover MSSQL from "Suspect Mode" and Errors

I think the script code may help you to resolve all are from SQL server...

EXEC sp_resetsatatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb ([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE [YourDatabase], REPAIR_ALLOW_DATA-LOSS)
ALTER DATABASE [YourDatabase] SET MULTI-USER

and second suggestion share the forum post: http://www.bubblews.com/news/3733174-how-to-recover-ms-sql-server-database

Hi Dear, Thanks for sharing

Hi Dear,

Thanks for sharing this tips!!!

I want to share something about DBCC CHECKDB minimum repair options: repair_fast, repair_rebuilt & repair_allow_data_loss. Most of the users think minimum repair options are the last resort to recover database from suspect mode but it is not true. As you know, there is some amount of data loss in repair_allow_data_loss option. I recommend to try advanced sql database repair software to recover database from suspect mode if you don’t want to loss any data from database.

Thanks
Mark
http://www.mssqldatabaserecovery.com/

MS SQL is an application

MS SQL is an application produced by Microsoft which is used broadly for efficient data management by many organizations around the world and has really become an indispensable need of users all over. SQL or the Structured Query Language helps the users to query the databases and also to easily retrieve information from databases that had been made already. In this MS SQL Server, the files are saved in .mdf file format.
For more information and free download
http://sqldatabserecovery.weebly.com
http://sqldatabserecovery.weebly.com/recovery-for-sql.html

SQL Server database from a

SQL Server database from a failure, a database administrator has to restore a set of SQL Server backups in a logically correct and meaningful restore sequence. SQL Server restore and recovery supports restoring data from backups of a whole database, a data file, or a data page, as follows:- http://www.pcrecoveryutility.com/sql-database-recovery.html

DATABASE suspect mode

how to retrieve a old data in database suspect mode to normal

SQL Database Recovery

SQL database recovery software recovers damaged MDF File, you can download third-party software, Kernel for SQL Database Recovery Tool. This software easy to repairs corrupt MDF file of SQL Server database. And that software also recover tables, triggers, indexes, keys, constraints, rules, and defaults. Click here - http://www.sqlrecoverytool.mdfrecovery.org

Try more reliable and advance

Try more reliable and advance SQL Database Recovery software. This software easy to recover all SQL data including tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, etc. Read more information and free download click here http://utilitysoftwarereview.com/sqldatabaserepair.html

Post new comment

The content of this field is kept private and will not be shown publicly.