Today I fucked up the production database by accidentally running a development script on it

1) I work in a very large environment and don't usually need to deploy development changes, but sometimes I do (when provided them from a vendor).

I insist on a rollback plan every time. Often that plan is as simple as backup and restore. I make sure I do it every time. Sometimes this pisses people off - they don't want to wait 15/30/60 minutes for the copy-only backup to complete (hey why not just take a diff backup or snapshot). I'll tell you why.

a) Because it's too easy for someone to have logged on earlier and done a full backup and discarded it, fucking up your backup chain and making your incremental useless. "Oh that never happens if you have everything secured!" It happens all the time and inevitably happens to me. If you have bulletproof scripts to verify the last backup exists where you expect it and the log chain is going to work then great - otherwise avoid.

b) There are tonnes of issues with snapshots and availability groups, it doesn't work the way you expect or at all. It's simply not worth it if you need to rollback, because then you need to take another backup and restore anyway in order to get the replica going. You may as well have taken a backup in the first place. Also places only use AGs in their production environment and not so much the others; this just kills you when your deployment plan has to change depending on environment.

2) I'm not sure what creates your scripts. If you were using SSDT you can include a test at the front to abort the script if the environment isn't what you expected. You shouldn't be running those scripts manually anywhere.

Basically I would not look for solutions about how to fix your manual process. You need to do something to automate your solution. It's going to be harder to screw up a production deployment when you need to run it from a command line and specify the environment each time. And yet still simpler than running it in SSMS.

/r/SQLServer Thread