Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database is marked inaccessible

Posted on 2001-08-09
10
Medium Priority
?
749 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 900 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

718 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