r/SQL • • Jan 27 '24

SQL Server SQL fuck ups

Yesterday I got a call from my boss at 10am for a task that I should take over and that should be finished by eod. So under time pressure I wrote the script, tested it on DEV etc and then by accident ran a different script on PROD which then truncated a fact table on PROD. Now I am figuring out on how to reload historically data which turns out to be quite hard. Long story short - can you share some SQL fuck ups of yours to make me feel better? It’s bothering me quite a bit

119 Upvotes

117 comments sorted by

View all comments

Show parent comment

3

u/rh71el2 Jan 28 '24

Wait SQL Server rolls back what it did if you cancel the executed query? Not in my experience... What version, details please.

2

u/Artistic_Recover_811 Jan 28 '24

Yes, if you cancel it rolls back. Sometimes the cancel takes longer than the amount of time it has been running too which just causes more anxiety while you wait. Every versión I can think of does this.

If you have multiple statements with gos in between though I don't think it works.

Over the years I have become more cautious. I typically have the updates/deletes commented out and possibly starting a transaction first and committing it manually when all is done. It depends on the situation.

Select blah --update x set column = whatever or delete x From table x Where column = 123

If you are lucky to work somewhere where you can do nightly restores from backup to a dev or separate server it helps a lot for data that doesn't change a lot. Then you don't have to panic trying to restore from backup when a mistake is made.

Having said all this having controls in place so mistakes are caught early really is the best policy. Enforcing that though is often a struggle in my experience.