Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10075
  • Last Modified:

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
0
RockyFullen
Asked:
RockyFullen
  • 5
  • 4
  • 3
  • +3
1 Solution
 
AustinSevenCommented:
The very first thing to do, if you haven't already tried, is to restart SQL Services.    Let me know about this first before we investigate other options.

AustinSeven
0
 
RockyFullenAuthor Commented:
Yes, I rebooted the system, and still the same problem
0
 
AustinSevenCommented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
AustinSevenCommented:
Also, have a look at this link for another option...

http://www.winnetmag.com/Windows/Article/ArticleID/492/492.html

AustinSeven
0
 
jkalahastyCommented:
The first step is to shut down SQL Server and bring it back up.

SP_resetstatus
DBCC DBRECOVER (database_name)

0
 
AustinSevenCommented:
BOL is useful as well... just start Books On-line and search for 'suspect'.   Some good info in there.

AustinSeven
0
 
ShogunWadeCommented:
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.
0
 
Partha MandayamTechnical DirectorCommented:
0
 
RockyFullenAuthor Commented:
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.....
0
 
arbertCommented:
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?
0
 
RockyFullenAuthor Commented:
no, how do you do it
0
 
RockyFullenAuthor Commented:
ok, Itried it, but it did not work.
Is there any hope?
0
 
arbertCommented:
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
0
 
AustinSevenCommented:
Here is a procedure that I took from the link provided by mcp111.   I was going to suggest the same kind of procedure but it saves me some typing...


1. Create a new database with the same name or a different name. You will have to use a different physical file name, which is fine.
2. Stop SQL Server.
3. Rename the new data file that was created to something else (ex: add.bak to the end)
4. Rename the old data file that you want to restore to the name of the newly created file (the same name as the file you changed in the step above)
5. Start SQL Server
Now the db will still be suspect but you now have a log file.
6. Switch to emergency mode on the database as homeri14 suggested.
7. Stop and restart SQL Server.
8. If database is still in emergency mode, run sp_resetstatus '', restart SQL Server.

Let us know how you get on.

AustinSeven
0
 
ShogunWadeCommented:
"Shouldn't have detached...."   i agree completely.    Attaching a corrupt database is nigh on impossible
0
 
jkalahastyCommented:
did u try doing this as i mentioned above:

SP_resetstatus
DBCC DBRECOVER (database_name)
0
 
arbertCommented:
"SP_resetstatus
DBCC DBRECOVER (database_name)"


Doesn't work on a detached database....
0
 
Partha MandayamTechnical DirectorCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now