Solved

Database is marked inaccessible

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

17 Experts available now in Live!

Get 1:1 Help Now