To recover from suspected database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

To recover from suspected database

Author

Topic

Ronesh
Starting Member

33 Posts

Posted - 2009-06-04 : 01:16:20

My database became suspect while trying to restore when the power cut off.so please help me to recover it ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-04 : 03:00:58

Check the SQL error log. There will be a reason that the DB is suspect and there will be errors in the error log that state why the DB has been marked suspect. Find those errors and post them here.

--
Gail Shaw
SQL Server MVP

Ronesh
Starting Member

33 Posts

Posted - 2009-06-04 : 06:13:26

As i am new to database field i know little only.so please pardon me if i do or ask something thats silly.

my database name is 'PumCrystal001'

in sysdatabases
before suspect mode
database status=4194328,mode=0
after suspect
database status=4194360,mode=51

the sql server error log is as under

Starting up database 'PumCrystal001'.

Cannot associate files with different databases.

Log file 'D:\PumoriDatabase2000\PumCrystal001_log.ldf' does not match the primary file.
It may be from a different database or the log may have been rebuilt previously.

By the way thank you Shaw for your Reply.
Looking froward.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2009-06-04 : 09:37:28

Since you were trying to restore the database in the first place, I would just drop the database (it is likely half a restore), then restore from the same backup you were originally restoring from. If you want to be truly safe, restore the database under a new name, with new names for the physical files, and check to see if the backup file/tape is actually good. If it is, then drop the suspect database, and rename the good database.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2009-06-10 : 10:38:19

sp_resetstatus 'yourdb' will reset

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-10 : 11:47:42

Maybe, but the underlying problem needs to be fixed before that can run otherwise it will not work.
It's also worth nothing that sp_resetstatus is deprecated in SQL 2005 and will be removed in a future version

--
Gail Shaw
SQL Server MVP

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2009-06-15 : 11:37:22

Hi Gila master, i've faced the same question in my interview with IBM.
i told them that sp_resetstatus will resolve this issue. can you(all of you) just share experience in this regard? what is the exact way to resolve this issue?

Arnav
Even you learn 1%, Learn it with 100% confidence.

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-06-15 : 15:27:14

Try this...
detach the suspect database, then attach it selecting the file(s) new location

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-15 : 18:08:12

quote:
Originally posted by laddu

Try this...
detach the suspect database, then attach it selecting the file(s) new location

Absolutely not. That's very dangerous advice. Very often if a suspect database is detached it's not possible to reattach it.

In fact, in SQL 2008 it's no longer possible to detach a suspect database.

See these blog posts
http://www.sqlskills.com/BLOGS/PAUL/post/Common-bad-advice-around-disaster-recovery.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-15 : 18:13:08

quote:
Originally posted by sunsanvin

i told them that sp_resetstatus will resolve this issue. can you(all of you) just share experience in this regard? what is the exact way to resolve this issue?

sp_resetstatus will only work if the underlying cause of the database been suspect has been fixed. If you don't fix the underlying cause first, trying to reset the status will do nothing.

As for how to fix the underlying cause, that depends completely on what's wrong. There's no simple one-size-fits-all solution to this.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources