Restored database in permanent loading state. Error 927.

Hi,

I am restoring a database (dbOrig) as I have done many times before (create an empty database (dbTrain) & restore the backup copy (dbOrig) over the top of it). After just 10 or so seconds SQL Enterprise Manager reports that it has successfully completed the restore. Unfortunately the database icon is greyed out and word "loading" has appeared to the RHS of the database. When I access the database properties I get an error message "Error 927" stating that the database is still in the middle of a restore.

The database (dbOrig) is quite small so the restore should be quite fast. I have left the database for a few hours but without any luck.

I have taken the backup and restored it to another system without any difficulty. I have taken this backuo (dbOrig) and tried again on another empty database. The problem reappears.

I have researched the problem on the Web and there is quite a bit of material but few answers. A common suggestion is to run the command -

restore database <locked database name> with recovery

from Query Analyser. I have done so but I only get an error message -

"File 'mydata' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."

Any help would be gratefully appreciated. I have already spent considerable time on this (resarched Web, Microsoft, etc.).

Thanks,

Dave
HelpPilgrimSystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wimthepimscakeCommented:
Hi Dave,

Dave,

That is a corrupted db, I also experienced this before, try to restore it again. Then to get rid of the corrupted one, try to detach it.

good luck

Wim
Wimthepimscake
0
SjoerdVerweijCommented:
- Delete the database in Enterprise Manager
- Go to the data folder and delete any MDFs that look applicable to the database
- Go to the log folder and delete any LDFs that look applicable to the database
- Reapply SP3 for SQL Server (on the server and especially -- if applicable -- the workstation you are restoring on)
- Try again.

Especially the second and third step sometimes do the trick. Ensure the file paths and names are correct (in the options tab while restoring).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HelpPilgrimSystemsAuthor Commented:
Thank you both for your comments.

I had already tried as Wim suggested. I had also tried the first foiur steps suggested by SjoerdVerweij. I have not yet tried to reapply SP3 for SQL Server. I shall do so and advise you when done.

Thank you,

David
0
HelpPilgrimSystemsAuthor Commented:
The reinstallation of SQL Server SP3a worked!!! I have been looking for a solution to this for a while. I had problems installing SP 3a though. It experienced errors running the first sql script. I tried the restore again despite problems with SP3a. It worked. Thank you!!

David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.