SQL 2012 Restore Database dialog performs tail-log backup on wrong database - by Mike Edenfield

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 776729 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 1/16/2013 11:07:17 AM
Access Restriction Public


When using the Restore Database dialog to restore a backup, you are given the option to perform a tail-log backup before starting the restore.

It appears that this option always performs a backup of the database name specified in the backup media, even if you manually change the database name to restore to a different database. The tail-log option is defaulted to on as soon as you select an existing backup to restore, so if you don't know to go turn it off it will leave your original database in RECOVERY mode (or possibly fail the restore).
Sign in to post a comment.
Posted by wigit0 on 12/19/2013 at 12:46 AM
I agree with DBAChad: I can't see how this can be a meaningful design decision where the target database is not the one from which the source backup was taken. As DBAChad says, the only way the tail log/restoring step makes any sense is where the target database already exists, and even then only if it is performed on that database. At the very least the user's attention should be drawn to the fact that the source database will be removed from service even though it takes no part in the restore process.
Posted by DBAChad on 5/6/2013 at 1:00 PM
I know this is old, but I just experienced this issue today and checked Connect to see if it had been previously reported. Fatemeh's suggestion to uncheck the "Leave source database in restoring state" does work, but I don't believe that is intuitive and there may be a better solution.

If you are restoring a database with a different name from that stored in the backup media, the database that should get the tail log backup is the one that has the name you are restoring to (i.e. the database you are restoring on top of, if it exists), not the one named in the backup media.

Note: This appears to be the same issue as Connect item 763746, which is currently marked as closed-duplicate.
Posted by Microsoft on 2/20/2013 at 4:34 PM
If database has snapshots, you cannot restore unless you restore to a new database (by renaming as suggested).
To restore a backup to a new database with a new name:

-    On general pane, load the backup device and change the name of the destination database
-    On files pane, check the relocate option and change file names
-    If you have snapshots on your database, or if you don’t want the state of the original database to stay in “Restoring”, on options pane, uncheck “Leave source database in restoring state”.

If the last step doesn’t solve your problem, please let us know.

Fatemeh Alavizadeh
Posted by Microsoft on 1/29/2013 at 2:00 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.