Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Suspect Database recovery

Posted on 2004-10-07
18
Medium Priority
?
10,066 Views
Last Modified: 2012-08-14
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
Comment
Question by:RockyFullen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
18 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12249581
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
 

Author Comment

by:RockyFullen
ID: 12249677
Yes, I rebooted the system, and still the same problem
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12249740
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 10

Expert Comment

by:AustinSeven
ID: 12249805
Also, have a look at this link for another option...

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

AustinSeven
0
 

Expert Comment

by:jkalahasty
ID: 12249859
The first step is to shut down SQL Server and bring it back up.

SP_resetstatus
DBCC DBRECOVER (database_name)

0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12249921
BOL is useful as well... just start Books On-line and search for 'suspect'.   Some good info in there.

AustinSeven
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12250366
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
 
LVL 6

Expert Comment

by:mcp111
ID: 12250709
0
 

Author Comment

by:RockyFullen
ID: 12251275
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
 
LVL 34

Expert Comment

by:arbert
ID: 12252512
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
 

Author Comment

by:RockyFullen
ID: 12253847
no, how do you do it
0
 

Author Comment

by:RockyFullen
ID: 12253894
ok, Itried it, but it did not work.
Is there any hope?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12253985
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
 
LVL 10

Accepted Solution

by:
AustinSeven earned 1500 total points
ID: 12256748
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12257322
"Shouldn't have detached...."   i agree completely.    Attaching a corrupt database is nigh on impossible
0
 

Expert Comment

by:jkalahasty
ID: 12259672
did u try doing this as i mentioned above:

SP_resetstatus
DBCC DBRECOVER (database_name)
0
 
LVL 34

Expert Comment

by:arbert
ID: 12260827
"SP_resetstatus
DBCC DBRECOVER (database_name)"


Doesn't work on a detached database....
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12262481
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question