RockyFullen
asked on
Suspect Database recovery
Hi,
yesterday my server took a dump. Today, I find several databases which are showing to be suspect, and empty.
The real problem is that my maintenace plan has been failing for a month, therefore I have no backup. How can I restore the suspect database?
Regards
yesterday my server took a dump. Today, I find several databases which are showing to be suspect, and empty.
The real problem is that my maintenace plan has been failing for a month, therefore I have no backup. How can I restore the suspect database?
Regards
ASKER
Yes, I rebooted the system, and still the same problem
Can you detach the database? ie. from Query Analyzer, use... exec sp_detach_db 'dbname' ? Often you won't be able to detach but it's worth a try. If you can detach it, the aim would be to attempt to re-attach using sp_attach_single_file_db.
AustinSeven
AustinSeven
Also, have a look at this link for another option...
http://www.winnetmag.com/Windows/Article/ArticleID/492/492.html
AustinSeven
http://www.winnetmag.com/Windows/Article/ArticleID/492/492.html
AustinSeven
The first step is to shut down SQL Server and bring it back up.
SP_resetstatus
DBCC DBRECOVER (database_name)
SP_resetstatus
DBCC DBRECOVER (database_name)
BOL is useful as well... just start Books On-line and search for 'suspect'. Some good info in there.
AustinSeven
AustinSeven
first and formost I would put it into emergency mode. this will allow you to access the data in the databases and pull it out into some form of backup. I would do this before trying to recover.
Here's how to fix a suspect db
http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=125378
http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=125378
ASKER
OK, I managed to detach the databases, however when I run
sp-attach_single_file_db there is an error
Server: Msg3624, Level 20, State1, Line 1
Any ideas.....
sp-attach_single_file_db there is an error
Server: Msg3624, Level 20, State1, Line 1
Any ideas.....
Ya, you should NEVER detach a suspect database--the chance of reattaching it afterwards isn't that great....
"OK, I managed to detach the databases, however when I run
sp-attach_single_file_db there is an error
Server: Msg3624, Level 20, State1, Line 1"
Did you also try sp_attach_db using the logfile as well?
"OK, I managed to detach the databases, however when I run
sp-attach_single_file_db there is an error
Server: Msg3624, Level 20, State1, Line 1"
Did you also try sp_attach_db using the logfile as well?
ASKER
no, how do you do it
ASKER
ok, Itried it, but it did not work.
Is there any hope?
Is there any hope?
Shouldn't have detached....If the data is important and you don't have a backup, I would consider calling Microsoft PSS, or looking at http://www.mssqlrecovery.com
Brett
Brett
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Shouldn't have detached...." i agree completely. Attaching a corrupt database is nigh on impossible
did u try doing this as i mentioned above:
SP_resetstatus
DBCC DBRECOVER (database_name)
SP_resetstatus
DBCC DBRECOVER (database_name)
"SP_resetstatus
DBCC DBRECOVER (database_name)"
Doesn't work on a detached database....
DBCC DBRECOVER (database_name)"
Doesn't work on a detached database....
AustinSeven