We have a guy at work (sorry, he’s from India) running update without specifying a where clause directly on the live database. The result is that the records of the entire table are updated, although he only needed to change one. Well, it happens to everyone, I hope that it will be rare and will bypass me.
The most annoying thing is that he did everything according to the rules – he opened a transaction, launched an update, saw that 11 thousand lines were changed instead of one, and for some reason ignored and committed the Wikipedia data. Only then did I see my joint.
Only 11 thousand rows, but in a fairly important table. Restoring a database from a backup takes too long. You need to request the last copy from the admins, get permission to copy 300 gigabytes of PCI restricted data, restore somewhere on the server and store the data. Unreal. It’s easier to update all the overwritten data with random garbage and say that Bill Gates is to blame for everything.
Then we decided to try to recover the data. We do a lot of reports and various data conversions, so the first thing we did was check all the places where there could be a copy of the data. No luck, nothing happened.
You can restore new records that have been created since the last full copy by viewing the records in the log, which is stored on the distributor in the distribution database. Just run the command sp_browsereplcmds and it shows everything you need. Just filter the records by table by specifying the article_id parameter.
This method is not the most ideal, because it shows SQL queries that still need to be processed, but if there is not much data to restore, then you can use it. If there is a lot of data, then it would probably be better to use a full backup.