5

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.

3 Answers 3

8

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.

12
  • Could you explain? I'm not a DBA. How / why could this happen?
    – Pete Montgomery
    Mar 24, 2010 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, 2010 at 19:07
  • Does this risk destroying any data?
    – Pete Montgomery
    Mar 24, 2010 at 19:09
  • I used the GUI; I don't know the kind of restore I performed.
    – Pete Montgomery
    Mar 24, 2010 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, 2010 at 19:12
2

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.

1

Check the option in Backup job under maintenance plan Take the tail log backup and keep the database in Restoring state. To Over Come,disable the above said option and run the following query for each affected Database Restore database DB_NAME with recovery;

In you case it may happened,accidentally this option was marked,because for this option it is not necessary to put your mouse under the check box to activate it,mistakenly clicked to near by this make it activated. so when this jobs ran,it took the tail log backup and put the DB in Restoring state.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .