Solved

Database is marked inaccessible

Posted on 2001-08-09
10
727 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
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 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

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

832 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