Caution about Restoring via SSMS v17

Question

Caution about Restoring via SSMS v17

NJ-DBA

SSChampion

Points: 13834
More actions
November 5, 2018 at 10:21 pm

#416466

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

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

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

About SQL SSC Rookie Points: 43 More actions · Answer 1

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 More actions · Answer 2

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: 43 More actions · Answer 3

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: 161 More actions · Answer 4

sqlcl

SSC Enthusiast

Points: 161

November 6, 2018 at 5:26 am

#2012503

In SQL Server 2016 SP1 too.

webrunner SSC-Dedicated Points: 31715 More actions · Answer 5

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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

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: 442467 More actions · Answer 7

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: 13834 More actions · Answer 8

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.

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

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: 100946 More actions · Answer 10

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) A socialist is someone who will give you the shirt off *someone else's* back.

NJ-DBA SSChampion Points: 13834 More actions · Answer 11

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: 734434 More actions · Answer 12

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: 734434 More actions · Answer 13

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: 100946 More actions · Answer 14

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) A socialist is someone who will give you the shirt off *someone else's* back.