Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Edit: This is still a live prob, less than an hour after trying RESTORE ... WITH RECOVERY.

I backed up a SQL Server 2005 database and restored it to a new SQL 2008 instance. The restore was quick and successful. Everything was fine for an hour or so.

Suddenly, the database is now stuck in "(Restoring...)" state in Management Studio and has a green arrow icon, and my application login is failing!

Any advice? :-)

Edit: This is a live application. If I delete and try again, the hour or so's data will be lost.

share|improve this question

migrated from stackoverflow.com Mar 25 '10 at 22:29

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

This can be due to a number of reasons, but there are some things you can try. Try running the following command:

RESTORE DATABASE <database name> WITH RECOVERY 

If that doesn't do it, you can try deleting the database and restore it again.

share|improve this answer
    
Could you explain? I'm not a DBA. How / why could this happen? –  Pete Montgomery Mar 24 '10 at 19:05
1  
By default, the is left in a state ready for log file restores. This command brings it fully on line but no more log restores can be applied. I assume you've just done a FULL restore msdn.microsoft.com/en-us/library/ms191455.aspx "Relationship of RECOVERY and NORECOVERY Options to Restore Phases" –  gbn Mar 24 '10 at 19:07
    
Does this risk destroying any data? –  Pete Montgomery Mar 24 '10 at 19:09
    
I used the GUI; I don't know the kind of restore I performed. –  Pete Montgomery Mar 24 '10 at 19:10
    
Running the restore command will not cause you to lose data, it will only prevent you from applying any more log restores. –  jball Mar 24 '10 at 19:12

this is a long shot, but it could be an auto-close database with a rather large log. Auto-close databases are automatically shutting down themselves when no longer in use. By default, Express editions create databases as auto-close. When a database is opened, it runs recovery, and if the log is very large and there isn't any recent checkpoint, recovery can last a while, long enough to be visible in the Server Explorer or in SSMS. It's true that auto-close databases have some optimizations as to do a 'fast' start up, but it may be fooled by some corner cases into doing a full recovery.

To validate, check the auto-close status of the database is sys.databases.

Otherwise, check the ERRORLOG and/or the system event log for messages that would indicate why is the database going through recovery.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.