THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

SSMS 2012 Restore GUI Gotcha

Today I want to bring to your attention an issue in the SQL Server Management Studio 2012 restore GUI. In many ways the new restore dialog is nicer than the old one, with new features and added convenience – but, as is always the Achilles heel of GUI tools like this, if you don’t know what’s really going on it can bite you. I’m not sure what to call this issue, maybe just a UI design flaw. Technically it works as designed, and there’s nothing really wrong with it, so it’s not a bug. But I can imagine it really causing someone pain who is careless or doesn’t know what’s happening behind the scenes.

Restoring a copy of a database make take the original down.

Among the new features is some management of “Tail-Log Backups,” which is a wonderful idea. Taking a backup of the tail of the log will take down the database and put the whole end of the log into a backup file, preserving basically all modifications to the DB from the log. This is ideal, for example, at the last step of migrating a database using log shipping, because all the activity is preserved and the source database is locked against further modifications. The log chain is preserved in moving to the new copy of the database, or in a DR scenario, to your other server.

The problem is, I know a lot of people don’t know this. Often its people who use the GUI a lot, such as “accidental DBAs.” I think the GUI is really important despite those who rarely use it, or frown on it.

Here’s the issue: Suppose I have a production database that has some user-created problem (like a user accidentally updated some rows.) It may be reasonable to restore a copy of the database to a point before the issue and investigate whether it’s possible to merge the data back in. The original database isn’t damaged from a technical point of view – there’s no corruption, for example.

  1. Right-click the original source database and choose Tasks > Restore > Database … from the context menu.
  2. The resulting dialog will go out and discover the backup files for that database from MSDB, which is very helpful.
  3. Change the name of the target database, in order to restore it as a copy.

RestoreGUIIssueCap1

There’s a note at the top of the dialog that, while accurate, may not help some in this scenario. It says, “A tail-log backup of the source database will be taken.” Sounds innocuous if you don’t know what that implies. In past versions, restoring a copy of a database would never affect the original – or not by default, anyway – so I cringe as I imagine people merrily clicking past this warning.

The script you get with these settings is indeed composed with a tail-log backup of the source database:

RestoreGUIIssueCap2

That will, as indicated, take a tail-log backup – and thereby take the original database down. Nice if you wanted that, but a mean surprise if you didn’t.

If you act on the warning and click the Options tab, and then uncheck the offending setting, you do get the expected behavior (the original database is unaffected because the tail-log backup is not included at the top of the script):

RestoreGUIIssueCap3

So, be careful out there!

Published Friday, March 15, 2013 10:30 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg Low said:

Yes, I've been concerned about this for a while. It's too easy to overwrite the original database. A while back I posted a Connect item about it. Please all vote for it if you are concerned: https://connect.microsoft.com/SQLServer/feedback/details/779190/sql-server-2012-ssms-silently-overwrites-db-name-and-can-cause-accidental-overwrites-and-data-loss

March 15, 2013 8:10 PM
 

merrillaldrich said:

Upvoted - thanks for submitting that one too.

March 26, 2013 10:24 AM
 

Bhavya Arora said:

gud article

May 10, 2014 4:56 AM
 

Peter Pappas said:

Great Article!  I ran into this issue and this article helped me understand what happened and why.  

May 30, 2014 11:55 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement