1

Is there a way to prevent SQL Server Management Studio 2014 from automatically setting the destination database when you try to restore a backup to another database?

I just had a nasty surprise when I wanted to restore a "somewhat fresh" backup of the production DB on the acceptance, and noticed after the restore (+Overwrite database) started not on acceptance but production. I ruined a whole 6 hours of business work (thanks IDE to know better than me how to get me fired...)

4

No and as you're aware this is a pretty serious accident to make.

I'd suggest learning a little PowerShell. Why? Because that kind of restore is going to happen often, and you could script it into an agent job or one liner - and you won't make this mistake again. It's one of the great parts of scripting; reducing human error and improving the script each time rather than having to remember everything yourself.

As for other suggestions

  • You can backup from an account on PROD that can only backup and doesn't have restore privileges.
  • Never do just a restore. Always do a backup (with copy only) of what you're overwriting before the restore. Every time. (If you're extremely confident of your existing backups and can run a quick verify of them... eg that you've taken a log backup in the last minute or so and the log chains aren't broken and that the files are there... you may get away with that too).

I can tell you there's been a number of times I've been given specific server and database names, had them double checked, then had someone realise while its in progress that THEY were wrong and had mixed something up. Having a quick rollback on hand is a godsend.

  • Why use PowerShell when it can be done much easier with TSQL? Not that I am against learning it, but this would not be much of a reason to learn it to me. It is a completely different syntax to do this in PowerShell, even using the cmdlets with SQLPS. – Shawn Melton May 29 '16 at 21:48
  • A PowerShell solution would be one job, one step, one self contained script which follows a logical flow. I'm not sure how you plan to implement it in pure TSQL while coordinating between servers, but something that comes to mind is one job on each, with the destination server job either running at a predetermined time assuming the backup is there or polling for disk changes. I don't know the official term for a process like that, or if you have something else in mind, but I think it's unnecessarily poorly coupled, and takes more effort to untangle for an outsider than a self contained script. – Cody Konior May 30 '16 at 1:36
1

If you used the IDE to perform the restore and not TSQL then you should get in the habit of verifying what server you are connected to before hitting ok.

If you use TSQL then you can utilize registered servers in SSMS that will let you color code you query tabs. So you can use something like red for your production servers to indicate you need to be careful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.