It's a test environment, I needed some data to test an Update query, but accidentally updated a column in all rows to have wrong data. Must I use a backup to restore the data back to the previous instance, or is there some secret with transaction log that I can take advantage of?
Thanks in advance.
There is a non-secret transaction log called transaction log that you can recover from to a point in time. Here's how... That annoying little file with the ldf extension is the transaction log, as opposed to the .mdf file that is your normal db data.
Unless you have truncated the transaction log (ldf) or otherwise mucked with it, you should be able to do exactly the kind of restore (undo) that you're looking for.
If your database was in full recovery mode then you can try reading transaction log using third party tool such as this one or you can try doing this yourself with DBCC LOG command.
If db is in full recovery then a lot of data is stored in transaction log but it’s not easily readable because MS never polished official documentation for this and because it’s purpose is not recovery but making sure transaction is committed correctly.
However there are workarounds to reading it like using the tool above (paid tool unfortunately but has a trial) or decoding the results of DBCC LOG yourself.
Not unless you wrapped your sql in a transaction block - begin transaction, rollback, commit. That one of the dangerous things about sql server. With Oracle you have to physically commit each transaction which is much safer imho.
