Caution about Restoring via SSMS v17

  • NJ-DBA

    SSChampion

    Points: 13768

    Comments posted to this topic are about the item Caution about Restoring via SSMS v17

  • About SQL

    SSC Rookie

    Points: 42

    I'm using 17.8.1 version of SSMS but default for me is "RESTORE WITH RECOVERY" so I don't undestand your article.
    Where i mistake ?

  • dperdices

    Valued Member

    Points: 63

    I think he refers to the second check marked in yellow.

    This two options as far I remember have been checked for default if the database is in full or bulk logged recovery model.

  • About SQL

    SSC Rookie

    Points: 42

    Yes, in my case SQL is with "RECOVERY SIMPLE" so all is ok but i think was better if you specify this condition in your article.
    Thanks

  • sqlcl

    SSC Enthusiast

    Points: 131

    In SQL Server 2016 SP1 too.

  • webrunner

    One Orange Chip

    Points: 29627

    Thanks for this heads-up. I've seen the tail-log option appear for me as described in the article. Luckily I did check the different dialog boxes and noticed it, so I was able to uncheck it. In my case it was only for a non-production database, but seeing the tail-log option checked by default certainly caught my eye.

    Thanks again,
    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • sqlfriends

    SSC Guru

    Points: 52310

    Thanks for the article, that is a good reminder.

    Not sure why microsoft make the checkbox checked as default? why the source database has to be in nonrecovery mode?

    Thanks

  • Lynn Pettis

    SSC Guru

    Points: 442094

    sqlfriends - Tuesday, November 6, 2018 9:26 AM

    Thanks for the article, that is a good reminder.

    Not sure why microsoft make the checkbox checked as default? why the source database has to be in nonrecovery mode?

    Thanks

    If you are taking a tail log backup of the database you don't want additional transactions being applied.  Defeats the reason for a tail log backup.

  • NJ-DBA

    SSChampion

    Points: 13768

    Lynn Pettis - Tuesday, November 6, 2018 9:42 AM

    sqlfriends - Tuesday, November 6, 2018 9:26 AM

    Thanks for the article, that is a good reminder.

    Not sure why microsoft make the checkbox checked as default? why the source database has to be in nonrecovery mode?

    Thanks

    If you are taking a tail log backup of the database you don't want additional transactions being applied.  Defeats the reason for a tail log backup.

    Yes, but why make it the default to take a tail log backup... just because I'm restoring a copy of another database, doesnt mean I want that one taken offline.  In my case, I'm usually making a copy and keeping both online.

  • sqlfriends

    SSC Guru

    Points: 52310

    I found if you restore a copy to overwrite the same database, it makes sense to have that checked as default.

    As NJ_DBA, in my case I usually restore the database to a different database and keep both online.Then check that as default, makes no sense.

    Thanks,

  • ScottPletcher

    SSC Guru

    Points: 97941

    Interesting ... do DBAs really use the gui to do backups and restores?  I never have.  For one reason, then I have no accurate record of what was done.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • NJ-DBA

    SSChampion

    Points: 13768

    I use the GUI from time to time in unusual cases... a one off restore that I'm unlikely to repeat.  In the case that inspired this post, I was looking to compare data in a non-production environment from a day ago just to make sure things happened the way I thought they did.  It was faster for me to just do a quick restore through the gui than via t-sql because I would have to look through the filesystem for the name of the time I wanted.
    It's pretty fair criticism that we probably wouldnt use the GUI for a production system, but I could imagine some "accidental DBAs" or junior DBAs learning this the hard way.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714105

    NJ-DBA - Tuesday, November 6, 2018 9:53 AM

    Yes, but why make it the default to take a tail log backup... just because I'm restoring a copy of another database, doesnt mean I want that one taken offline.  In my case, I'm usually making a copy and keeping both online.

    I believe the expectation is that in an emergency, a pressure situation, you'll use the GUI quickly and you might make the mistake here. If someone drops a table, you might want the most recent restore, but you do want the tail log as well.

    If you're making a copy, then you  don't want this, but to be safe, this makes the most sense for a default option.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714105

    ScottPletcher - Tuesday, November 6, 2018 11:50 AM

    Interesting ... do DBAs really use the gui to do backups and restores?  I never have.  For one reason, then I have no accurate record of what was done.

    They absolutely do. And most people never have an accurate record of what they've done for restores, for new users, for changing mailboxes in Exchange,for altering Active Directory, et al.

  • ScottPletcher

    SSC Guru

    Points: 97941

    Steve Jones - SSC Editor - Tuesday, November 6, 2018 12:02 PM

    They absolutely do. And most people never have an accurate record of what they've done for restores, for new users, for changing mailboxes in Exchange,for altering Active Directory, et al.

    Wow, I'm surprised.  That's certainly interesting, and not what I expected.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply