Solved

Suspect Database recovery

Posted on 2004-10-07
18
10,057 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 ?
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.
Viewers will learn how the fundamental information of how to create a table.

827 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