Accidently Kicking a Database into the Restoring State

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

backuplog

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

13 Responses to Accidently Kicking a Database into the Restoring State

  1. Andy Warren says:

    I didnt know this either, interesting.

    Like

  2. Bill Barnes says:

    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?

    Like

  3. way0utwest says:

    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.

    Like

  4. Gail Shaw says:

    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.

    Like

  5. Vikas says:

    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.

    Like

  6. Gail Shaw says:

    @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

    Like

  7. Charles Kincaid says:

    Great to know. Thanks for pointing out.

    My question is: “Now how do I get out of this?”

    Like

  8. Sreekanth bandarla says:

    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

    Like

    • Charles Kincaid says:

      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!

      Like

  9. JeremyE says:

    A tail-log backup is also used when failing over log shipping.

    Like

Comments are closed.