News

Navigation

How to Repair SQL Server 2005 Suspect Database

If your Database is marked as Suspected, here are the steps to fix it :

EXEC sp_resetstatus 'DBName'
GO
ALTER DATABASE DBName SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBName', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBName SET MULTI_USER

-- Rebuild the index

ALTER INDEX ALL ON [ TableName] REBUILD

Good luck

sharon cohen shahen tov

Posted: Dec 28 2008, 12:14 PM by DBASharon | with 44 comment(s)

תגים:DEV, ITPRO

Comments

sql database repair said:

For work with similar type files try-Recovery Toolbox for SQL,as far as i know it is free,program repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

# January 3, 2009 11:16 PM

DBASharon said:

Thanks for the information i will check it later

# January 29, 2009 6:29 PM

Guest said:

thank you very much

i worked fine for me

thanks again

# June 5, 2009 10:53 PM

Arno said:

Thanks. Will be very useful.

# July 6, 2009 12:17 PM

MMB said:

Thanks

vey very Useful

# July 15, 2009 5:46 AM

Eric W said:

DBASharon -- whoever you are, I am forever in your debt. After days of futile efforts to salvage my 'suspect' database, the code you offer here revived it in minutes ... and may well have prevented my own suicide. You are my hero.

תוֹדָה!

# July 21, 2009 6:46 AM

ALI TOPRAK said:

THIS IS PERFECT..

MY DATABASE IS REPAIRED

THANKS THANKS...

HARIKA BIR KOMUTTU

INANMAMISTIM AMA DATABASIM KURTULDU GERCEKTEN..

ÇOK TEŞEKKÜRLER

# August 20, 2009 8:43 PM

Janus S. Andersen said:

Thanks

# August 21, 2009 2:30 PM

BOIGIA said:

WORKED PERFECT

THANKS

BEST

SOTHIKAME

# October 20, 2009 2:15 PM

Kost said:

Good tip.

Just confirming that this worked for SQL 2008 as well.

NOTE: I didn't run the sp_resetstatus proc

# October 29, 2009 1:38 AM

Jessica said:

Guys this works for me, too!!!

Thank you so much..

# November 17, 2009 11:54 PM

Shuk said:

Thanks for recover database but how i can recover store procedure.

SP is not recovered.

# November 25, 2009 11:07 AM

Vishal said:

Thanks a lot. It worked for me as well.

# December 20, 2009 5:52 PM

SQL SUSPECT o Invalida » Prodtech Blog said:

Pingback from SQL SUSPECT o Invalida » Prodtech Blog

# December 22, 2009 4:21 PM

Moups said:

Thanks a lot for the script.It worked for me as well

# January 8, 2010 1:55 PM

Joe said:

Hi Sharon,

Your instructions above helped spot on. The issue we had was on a SQL 2008 server for the report server database. After trying a failed attempt with different set of codes, your instructions above worked flawlessly to restore the suspect database. Thanks a ton!

# January 14, 2010 2:36 AM

Paul said:

Thanks for the script. IMHO this is just one part of the solution. Now you have an online db but you may have lost some data.

The big question now is, how do you check the integrity of the data - let's say you have broken a database that holds the data for an ERP solution, like MS Dynamics NAV.

# January 29, 2010 10:06 AM

Ibrahim Jr said:

Thanks for the script.

# February 24, 2010 4:17 PM

Habib CELIK said:

Thanks for All.

This Command is recovered the my database.. :)

Thanks..

# March 22, 2010 7:08 PM

Mark said:

Worked Great for me Thanks

# March 24, 2010 6:36 PM

Casey said:

This is great. Thank you so much! This simple script repaired a very large db that I had that had gotten corrupted...I had no data loss and it fixed my issues!

Thank you thank you!

# April 23, 2010 2:58 PM

VJ said:

You Rock!!!

# May 3, 2010 1:21 PM

Amit said:

Saved me, thanks

# May 4, 2010 10:35 AM

Dave Hill said:

Fantastic - following a SAN Array issue, this worked an absolute treat.

Handy on SQL Server 2008 also btw.

# May 6, 2010 6:15 PM

Rehatta Kesuma said:

I have a problem, my DB using SQL server 2005 have suspect, file MDF and LDF can not attach

this my link DB :

Please give me a solution for this

hotfile.com/.../mdf_n_ldf.rar.html

if you have a solution this, please send to dxa03176@yahoo.com

thank for your attention

# May 24, 2010 7:43 PM

Ajay Jamwal said:

Damn it !

It changes in to EMERGENCY MODE......

Any help ?????

# May 28, 2010 9:38 AM

How to recover a suspect database on MS SQL 2005 « The Joe Lake Blog – joeylakey.com said:

Pingback from How to recover a suspect database on MS SQL 2005 « The Joe Lake Blog – joeylakey.com

# October 16, 2010 1:21 PM

I thought I saw it somware said:

www.codeproject.com/.../SQL_2005_Suspect_Database.aspx

Please give credit since he published it 2 years ago....

# December 11, 2010 7:54 PM

IT ?????i Gi?? » How to Repair SQL Server 2005 Suspect Database said:

Pingback from IT ?????i Gi?? » How to Repair SQL Server 2005 Suspect Database

# January 10, 2011 7:00 PM

Imo said:

thanks so much

# January 24, 2011 3:32 AM

Asaf said:

If you would like to get information about corrupted pages then the following query would be quite useful;

Contains one row per page that failed with a minor 823 error or an 824 error. Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_type column

select D.name AS DatabaseName, F.name AS FileName,

SP.page_id, SP.event_type, SP.error_count

from msdb.dbo.suspect_pages AS SP

join sys.databases AS D ON D.database_id = SP.database_id

join sys.master_files AS F ON F.database_id = SP.database_id

AND F.file_id = SP.file_id

order by SP.error_count desc, sp.page_id

# February 3, 2011 11:52 AM

Azmy said:

Thank youuuuuu

# June 6, 2011 8:56 AM

Patrick said:

Worked fine, thank you so much for your help. I'm really great-full to you.

# July 5, 2011 3:48 PM

Vaclav said:

Thank you from Czech Republic !!! You ARE GOD !!!! :-)

THANK YOU !

# July 21, 2011 3:59 PM

Vijay said:

I run SAP B1 application on SQl server. It was in suspect mode. Completed recovery as per steps above. It was successful. But Few options does not work e.g. GRPO gives error as "Record already exists" so could not add any record in the table. One application gives error that SQl formation error.

PL suggest further repair.

Thanks,

Vijay.

# July 26, 2011 6:37 AM

Manuel Bidó said:

It's works...

Thank you!

# September 1, 2011 10:48 PM

Price said:

This worked AMAZINGLY!! Thank you so much, this was truly extremely helpful and worked like a charm!

# September 3, 2011 11:28 PM

C.V.S.RAMAN said:

FOR me it is working fine.

C.V.S. RAMAN

# September 12, 2011 3:24 PM

neet been said:

I'm really great-full to you.

Thank you very much!

Neet Been

# November 13, 2011 2:44 PM

ٍsaddam said:

thanx

# December 21, 2011 12:30 AM

Razeen said:

Thanks and it worked.

# January 17, 2012 9:40 AM

Kamil said:

Yes this script helped me to recover from database suspect state. Excellent

# February 3, 2012 2:27 PM

Pete Sheldrick said:

Many thanks. 25GByte database went Suspect, after a couple of restarts and using these procedures 100% working again.

# February 17, 2012 4:37 PM

Sandip said:

Thank you very much....It worked correctly..

Thanks a lot once again

# February 21, 2012 8:36 AM

SQL SERVER DBAces And More

Recent Posts

Tags