I learn new things all the time. This was one that actually stunned me. Huge props to Gail Shaw for posting a note about this in a thread.
Run this code:
CREATE DATABASE MyRestoreTest ; GO USE MyRestoreTest go BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak'; GO CREATE TABLE mytable( id INT) ; GO USE master go BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery
You’ll see this in your Object Explorer
Ugh.
I haven’t started a restore. I’ve run a backup. Apparently this causes problems, as noted by Gail in the thread. Sure enough, it’s documented in the BACKUP command, in the Log-specific Options.
I had never scheduled backups with this type of option, but you might have a job that does this if you were preparing for a failover. Having a script ready it a good idea, but if it executes unexpectedly, this could happen.
The lesson: make sure you know the options when you run a command. Always test, and if something strange happens, search or ask what might have happened.
Voice of the DBA 
Comments
13 responses to “Accidently Kicking a Database into the Restoring State”
I didnt know this either, interesting.
LikeLike
The only reason I could ever see this as useful is when you’re setting up mirroring and you don’t want anything to have to catch up. Any other thoughts to why this would exist?
LikeLike
To amend, also for the tail of the log recovery.
LikeLike
It’s also interesting that it only works if you have exclusive access – if there is another connection the backup fails. So in practice you’d have to put it in single user mode and then run the final w norecovery.
LikeLike
I’m not sure why this is there, other than preventing any changes after a tail log. I guess it’s a good idea to prevent any application/anyone from using the db after this point.
LikeLike
It’s a tail-log backup, for use in a disaster (when you’re planning to kick off a restore) or when moving a DB from one server to another. You wouldn’t use it when setting up mirroring.
Imagine you have to move the prod DB from server A to server B with minimal downtime and no lost transactions. Take a full backup, restore it on B WITH NORECOVERY, restore all of the log backups onto B with NORECOVERY, now you want to make the switch. Take the last backup on A (the tail-log backup) WITH NORECOVERY. The DB switches into restoring state ensuring that no more transactions can be run on A after the ones recorded in the last log backup. Take that last log backup over to B, restore it WITH recovery and the database on B is now online and usable.
LikeLike
It is already well known in SQL community and is known as taking Tail log backup. this is useful when your database is in suspect mode due to corruption and you need to restore the data from backup to the time the corruption occurred.
LikeLike
I’d argue this isn’t well known. I didn’t, nor did dozens of people I asked. It’s not clear this is what happens, or might happen, when you do this.
LikeLike
@Vicas If your database is suspect, this option is not what you’d use. In fact, when a DB is suspect a backup WITH NORECOVERY will fail. When the database is damaged you’d use BACKUP LOG … WITH NOTRUNCATE
LikeLike
Great to know. Thanks for pointing out.
My question is: “Now how do I get out of this?”
LikeLike
You can do a Restore with Recovery to complete the restore on this database in this scenario. That’s how you would get out of this.
Syntax: RESTORE DATABASE [MyRestoreTest] WITH RECOVERY
LikeLike
Thanks. I sure that there were some who don’t know.
It’s kind of like the training for aircraft ditching. You get strapped into the seat and they drop you into a cold water tank. But they also teach you how to get OUT!
LikeLike
A tail-log backup is also used when failing over log shipping.
LikeLike