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.

Pete Montgomery
  • 154
  • 1
  • 5

3 Answers3

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.

jball
  • 206
  • 1
  • 4
  • Could you explain? I'm not a DBA. How / why could this happen? –  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 http://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? –  Mar 24 '10 at 19:09
  • I used the GUI; I don't know the kind of restore I performed. –  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
  • Is anyone else working on this DB in addition you that might have been doing anything, or is it just you and the application? – jball Mar 24 '10 at 19:13
  • Just lil' old me. I ran the command and it seems to have been successful. –  Mar 24 '10 at 19:20
  • Good to hear! . – jball Mar 24 '10 at 19:22
  • It's happened again! Was fine, and now down and "Restoring" again! –  Mar 24 '10 at 20:18
  • Were you working in Management Studio at the time? If not, it must be something in the application that's causing it. – jball Mar 24 '10 at 20:45
  • No. I was logged in Studio, but I wasn't doing anything. How could an app cause this? –  Mar 24 '10 at 20:52
  • You should probably open another question specifically asking that; it's not something I've seen before - off the top of my head, it might be db log problems caused by uncommitted transactions? – jball Mar 24 '10 at 21:09
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.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
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.

Chandan Gupta
  • 11
  • 1