a_j_halpin
asked on
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
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
Have you tried to reset the server?
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
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
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.
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.
ASKER
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
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
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.
You could try it on your server but make sure you have copies.
Run this command from your query analyzer.
RESTORE DATABASE yourdb
WITH RECOVERY
and then reboot your server.
RESTORE DATABASE yourdb
WITH RECOVERY
and then reboot your server.
ASKER
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
Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.