Solved

Suspect Database recovery

Posted on 2004-10-07
18
10,055 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

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.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now