Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

Database is marked inaccessible

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
a_j_halpin
Asked:
a_j_halpin
  • 3
  • 2
  • 2
  • +3
1 Solution
 
FelixinCommented:
Have you tried to reset the server?
0
 
nigelrivettCommented:
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
 
TheSpiritCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
TheSpiritCommented:
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
 
a_j_halpinAuthor Commented:
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
 
nigelrivettCommented:
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
 
rgollakotaCommented:
Run this command from your query analyzer.
RESTORE DATABASE yourdb
   WITH RECOVERY
and then reboot your server.
0
 
a_j_halpinAuthor Commented:
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
 
mgrajkumarCommented:
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
 
a_j_halpinAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now