Grant Fritchey (11/7/2013)
Restoring or recovering?
A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY ....
In what might be an epic role reversal, don't you mean RESTORE LOG etc.?
No, I really do mean backup.
If you try a RESTORE LOG on a database that's online, all you'll get is an error (no files are ready to roll forward), the database state won't change. If you do a tail log backup (BACKUP LOG ... WITH NORECOVERY) then you're telling SQL you want to take a log backup and ensure that no more transactions can be done after the backup (it's done prior to starting a restore or moving a database). It will then do the backup (to DISK or whereever else) and once the backup completes the database will switch into the restoring state. This ensures that the log backup captured the very last transactions that ran so that none are lost.
The problem is, both the backup wizard and the maintenance plan backup task have the option 'back up the tail of the log' in them, if that's selected erroneously (especially in a maintenance plan), then the database will appear to switch into RESTORING for no good reason.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass