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

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

Closed

as By Design Help for as By Design

Type:	Bug
ID:	776729
Opened:	1/16/2013 11:07:17 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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).

Details (expand)

English

SQL Server 2012 - Enterprise Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 Professional

English

1. Take a backup of an existing database ("OriginalDB") on your SQL 2012 server
2. (Optionall) Take a snapshot of OriginalDB
3. Launch the Restore Database dialog.
4. Select your OriginalDB backup as the Source Device.
5. Rename the Destination Database to a new non-existant database ("NewDB")
6. On the Files tab, manually rename the physical files to match the NewDB name.
7. Click OK.

If there is a snapshot on OriginalDB, then the restore of NewDB fails with the error:

"This operation cannot be performed on a database with database snapshots or active DBCC replicas."

If there is no snapshot on OriginalDB, then NewDB is created and restored correctly. However, a tail-log backup of OriginalDB is taken, and OriginalDB is left in RECOVERY mode.

The restore would create NewDB using the new file names. OriginalDB would remain untouched.

The correct results can be achieved be manually deselecting the tail-log backup option in the Options tab before continuing.

X64

Hyper-V (On-Premise)

0 attachments

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.

Thanks,
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.

Thanks,

Alex Grach [MSFT SQL SERVER]

Posted by Mike Edenfield on 1/16/2013 at 11:07 AM

Manually disable the Tail-log option before starting the restore.