Solved

Suspect Database recovery

Posted on 2004-10-07
18
10,062 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

623 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