June 23, 2008 at 6:17 am
Hi Experts,
How to recover a database from suspect mode???:w00t:
TIA
June 23, 2008 at 6:31 am
Do you have a good recent backup and transaction log backups up to the point failure? When i've had this previoulsy i have had to restore from backup.
I know Paul Randall and some others have written some posts on on this site regarding this and the use of sp_reset. I'd look them up and see if the same situation applies to you and perhaps take their advise first.
Hope this help
Gethyn Elliswww.gethynellis.com
June 23, 2008 at 6:42 am
Depends why it's suspect. Check though the error log, see if you can find an entry in the log saying that SQL is marking the database suspect. It should give some reason why.
Do you have a backup?
SQL 2000 or 2005?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2008 at 6:57 am
How did you land up with a SUSPECT Database. There should be a hint in the errorlog or event viewer.
use can use the sp_resetstatus (dbname) SP to recover from a suspect DB.
Maninder
www.dbanation.com
June 23, 2008 at 8:14 am
if you have a corrupt log file you can attach it and create a new log. if it's a corrupt mdf file then you need to restore
June 23, 2008 at 11:43 am
Other Options:
SELECT state_desc FROM sys.databases WHERE name = 'dbname';
SELECT DATABASEPROPERTYEX ('dbname', 'STATUS');
ALTER DATABASE dbname SET EMERGENCY (This will only be available to members of SYSADMIN role)
Maninder
www.dbanation.com
June 23, 2008 at 1:35 pm
That's perfact, I will do the same. If you can not able to do that then probably Last backup is the best one.
MCP, MCTS (GDBA/EDA)
June 24, 2008 at 12:00 am
Manoj (6/23/2008)
That's perfact, I will do the same. If you can not able to do that then probably Last backup is the best one.
Actually the restore of last backup should be the prefered option with emergency mode and repair a last resort if there is no backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 6:04 am
Restart ur services or detach the database and attach it
June 24, 2008 at 7:14 am
[font="Verdana"]You can refer the link below, this might help you!
How to Recover from Suspect Database[/font]
Regards..Vidhya Sagar
SQL-Articles
June 25, 2008 at 2:26 am
Hi,
this worked a few months ago on my side:
/*
--1. step, repair
ALTER DATABASE kaputtDB SET EMERGENCY
ALTER DATABASE kaputtDB SET SINGLE_USER
DBCC CHECKDB (kaputtDB, REPAIR_ALLOW_DATA_LOSS)
*/
/*
--2. activate db
ALTER DATABASE kaputtDB SET Online
ALTER DATABASE kaputtDB SET multi_USER
*/
Regards,
Jan
June 25, 2008 at 7:53 am
I would recommend adding a step to Jan's solution.
run the CHECKDB without the REPAIR_ALLOW_DATA_LOSS first - the REPAIR_ALLOW_DATA_LOSS should send shivers down your spine :w00t:
then review the output from the CHECKDB to determine which objects are affected. Sometimes it is only an index so you can drop and recreate or go ahead and run the CHECKDB with the REPAIR. If it is a clustered index or heap you have to determine whether you can live with data loss - I have never tried to determine what data would be lost. At that point you hopefully can restore from backup.
You should of course attempt to determine what caused the problem in the first place and prevent reoccurance.
jg
June 25, 2008 at 2:54 pm
Jan's solution is dead on! We had several databases go into suspect mode after power failure. Including MSDB. I worked with Microsoft on the MSDB recovery and we deleted and restored that one from a backup. the rest we did the same as Jan states.
June 26, 2008 at 12:56 am
there was one suggestion to try detach and re-attach a suspect database. i think not. if truly the database is corrupted, the chances of reattaching it is very very slim (or next to impossible). beware!!
Simon Liew
Microsoft Certified Master: SQL Server 2008
June 26, 2008 at 2:17 am
How to change a database from emergency to normal???
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply