Solved

Database is marked inaccessible

Posted on 2001-08-09
10
731 Views
Last Modified: 2012-06-21
Setup:
SQL server 7.0 on win2000 server.

Situation:
Client tried to restore then cancelled during restore.  Database is now marked inaccessible.  Database icon is greyed out and has (loading) beside it.  If I try to run any stored procedures on the database it tells me that it is in the middle of a restore (Msg 927).  The client has no backups (unbeleivable I know) so cannot restore from backup.

Question:
How do I restore the database if I can at all.

regards,

Andrew
0
Comment
Question by:a_j_halpin
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 2

Expert Comment

by:Felixin
ID: 6368556
Have you tried to reset the server?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6368632
If they have no backups then what were they restoring from.

If it was cancelled in the middle then then the database has probably been lost now.

Do they have disk backups?
You might manage to restore the mdf and do a sp_attach_single_file_db
0
 
LVL 4

Expert Comment

by:TheSpirit
ID: 6368641
I think the problem may be that when the restore was being processed no-recovery or standby was specified. This is used to allow for a full backup to be restored then transaction log backups without going through the recovery process, you could try a restore with recovery (don't specify file to restore) this may bring you back to a working state.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 4

Expert Comment

by:TheSpirit
ID: 6368665
Just how did they cancel the restore ? There is a restart option that can be specified on a restore, I have never used it though.
0
 
LVL 1

Author Comment

by:a_j_halpin
ID: 6368821
Hi all,

they did not intend to restore from any backup, they merely wanted to see what backups had been performed by accessing the restore option.  There had only ever been one backup which was about six months old.  Instead of clicking cancel they clicked ok and imediately realising their error they canceled the restore.  There are no disk backups.  The MDF and LDF files seem to be intact and are the size I would expect them to be.

The restore was cancelled a split second after it had been initiated.  Do you think in this time it could have overwritten the database with corrupt data?

Thanks,


Andrew
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6369079
try copying the mdf and ldf to another server and doing a sp_attach or sp_attach_single_file_db

You could try it on your server but make sure you have copies.
0
 
LVL 1

Expert Comment

by:rgollakota
ID: 6370149
Run this command from your query analyzer.
RESTORE DATABASE yourdb
   WITH RECOVERY
and then reboot your server.
0
 
LVL 1

Author Comment

by:a_j_halpin
ID: 6383268
I have pretty much tried all the suggestions here and more.  I think that the database file itself is corrupted as I am constantly getting the error msg 925 - database could not be opened because some of the files could not be activated.  Has anyone ever been in this situation before?  Would there be some sort of third party utility that could perform a brute force rebuild?  DBCC commands give the same error. Help.

Andrew
0
 
LVL 1

Accepted Solution

by:
mgrajkumar earned 300 total points
ID: 6387371
check out http://support.microsoft.com/ for the Microsoft Knowledgebase.  Specifically Q165918 "INF: Bypass (Emergency) Mode and
DUMP TRANSACTION WITH NO_LOG".

Firstly look in <sql>\LOG and look at all recent errorlog(s).There WILL be an indication here as to
why the database has been marked suspect.  You need to fix whatever the problem is first (i.e. missing
file, permissions problem, hardware error etc.)

Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you
have no backup anyway, so you've nothing to lose, then change the database status to normal and restart
SQL Server.  To change the database status, and to get more information on recovery, look up the SP_RESETSTATUS
sp in the Books Online.

If you don't have access to sp_resetstatus information, then the short version of this is :-

UPDATE master..sysdatabases SET status = status^256 WHERE name = <dbname>

If the database still goes back into suspect mode, and you can't fix the original problem, and you have
no recent backup, then you can get information out of the database by putting it into emergency mode.
 If you do this, extract the data/objects out with BCP/Transfer Manager and then rebuild the database.
 Note that the data may be corrupt or transactionally inconsistent.

Issue the following command to put the database into emergency mode (you'll need to allow updates first)

UPDATE master..sysdatabases SET status=-32768 WHERE  name='<dbname>'
0
 
LVL 1

Author Comment

by:a_j_halpin
ID: 6405603
Managed to get some of the tables out using the transfer manager.  A lot of the stuff was corrupt though.  Its just bad luck really.  We crashed a similar database while doing a restore here in the lab and could completely recover using the DBCC options in emergency mode.  Oh well, time to find a good lawyer.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trigger C# code inside the SQL Server 6 36
Connect Gridview column to Textbox in C# 2 41
Access #Deleted data 20 43
Can a Trigger trigger a Trigger? 4 24
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…
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…
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, show how to setup several different housekeeping processes for a SQL Server.

730 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