Caution about Restoring via SSMS v17

Question

Caution about Restoring via SSMS v17

Viewing 8 posts - 16 through 22 (of 22 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

ScottPletcher - Tuesday, November 6, 2018 12:15 PM
Wow, I'm surprised. That's certainly interesting, and not what I expected.

Totally agree. There have been a lot of issues when people try to restore using SSMS - depends on the version as to what happens but there are differences in different versions of SSMS and often problems with whatever file lists need to be displayed.
Personally, I think relying on SSMS is a bad idea. Way too many issues with what people "see" in that restore screen. It makes me miss Enterprise Manager a bit....when no one could really depend on being able to even open it in some emergencies so DBAs really did need to know t-sql and running things from osql.

Sue

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

ScottPletcher - Tuesday, November 6, 2018 12:15 PM
Wow, I'm surprised. That's certainly interesting, and not what I expected.

I agree to both what you said and what you do in this area, Scott... and, as has been said, if you're in a hurry, that's when you're most likely to make a "mistrake". 😀 Even if I use the GUI for a quick job, I don't actually execute through the GUI. I have and always will generate the script and review it. It has saved my butt on more than one occasion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zNick SSC Eights! Points: 842 More actions · Answer 3

So, any interesting *terrible* stories behind this article? Feel free to share.... (Wanna get my Schadenfreude on...)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

zNick - Wednesday, November 7, 2018 3:31 AM
So, any interesting *terrible* stories behind this article? Feel free to share.... (Wanna get my Schadenfreude on...)

I guess the most "interesting" story that I have is that I've never had a problem with this type of thing because I don't actually ever execute directly from the GUI... I learned that a long time ago simply by being curious enough to gen the code the GUIs produce to try to learn something new. It turned out to be a very safe practice for me as things have changed, sometimes without warning, over the years. If someone does want to execute from the GUI, I'm not sure why some folks don't visit each and every page in the GUI they're trying to use especially in the face of such things as "upgrades".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thisisfutile Hall of Fame Points: 3668 More actions · Answer 5

If I recall, using the GUI on a production server, if you create a DB, like "Sandbox" and then Right-Click > Restore DB and then navigate to a BAK of your main production DB and don't change any of the default options, doesn't the default code try to overwrite the production MDF and LDF files? The "With Move" clause isn't in the default script. So, a new DB can think they're simply restoring the Sandbox db with a production backup but in turn they're trying to overwrite the actual production files with a backup. Does that sound right?

Sue_H SSC Guru Points: 90860 More actions · Answer 6

thisisfutile - Wednesday, November 7, 2018 7:29 AM
If I recall, using the GUI on a production server, if you create a DB, like "Sandbox" and then Right-Click > Restore DB and then navigate to a BAK of your main production DB and don't change any of the default options, doesn't the default code try to overwrite the production MDF and LDF files? The "With Move" clause isn't in the default script. So, a new DB can think they're simply restoring the Sandbox db with a production backup but in turn they're trying to overwrite the actual production files with a backup. Does that sound right?

As Jeff indicated previously, the issues depend on what version of SSMS. There have been more than a few posts up here on different issues people ran into related to backup/restores, using the GUI and different behaviors changing. The default behaviors have changed with different versions of SSMS.

Sue

thisisfutile Hall of Fame Points: 3668 More actions · Answer 7

Sue_H - Wednesday, November 7, 2018 8:04 AM
As Jeff indicated previously, the issues depend on what version of SSMS. There have been more than a few posts up here on different issues people ran into related to backup/restores, using the GUI and different behaviors changing. The default behaviors have changed with different versions of SSMS.
Sue

I did not glean that from Jeff Moden's two posts but I definitely agree with it. My takeaway from his posts was to generate the script and review but after re-reading, I can see where you got that. Judging by your post, and I may be gleaning wrong (and I'm definitely using the word 'glean' too often in a single response) potentially one or all versions could be doing what I described. Thanks for responding to my question. I stopped using the GUI a long time ago and have my own scripts that I use, version after version.

Sue_H SSC Guru Points: 90860 More actions · Answer 8

thisisfutile - Saturday, November 10, 2018 9:57 AM
Sue_H - Wednesday, November 7, 2018 8:04 AM
As Jeff indicated previously, the issues depend on what version of SSMS. There have been more than a few posts up here on different issues people ran into related to backup/restores, using the GUI and different behaviors changing. The default behaviors have changed with different versions of SSMS.
Sue
I did not glean that from Jeff Moden's two posts but I definitely agree with it. My takeaway from his posts was to generate the script and review but after re-reading, I can see where you got that. Judging by your post, and I may be gleaning wrong (and I'm definitely using the word 'glean' too often in a single response) potentially one or all versions could be doing what I described. Thanks for responding to my question. I stopped using the GUI a long time ago and have my own scripts that I use, version after version.

Sorry my bad. Thought he had mentioned that before. Different versions of SSMS have different behaviors with different restores. I don't use the GUI for backups or restores but I'm aware of too many posts with different behaviors. Defaults, files displayed are different with different versions. Last time I was playing with it related to a post up here, the restore was different for three versions of SSMS that I used to try to replicate the issue in the post. I do remember some posts where moving the files (with move) was problematic but can't remember the specifics so not sure if it's related to what you saw. I wouldn't doubt it though.

Sue