A New Recovery Tool for Your Toolbelt

Question

A New Recovery Tool for Your Toolbelt

Steve Jones - SSC Editor

SSC Guru

Points: 736418
More actions
November 14, 2016 at 11:26 pm

#337298

Comments posted to this topic are about the item A New Recovery Tool for Your Toolbelt

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

These temporal tables look as they could be totally misused. As well as being really useful. Give it 12 months and I am sure that I will be reading an article with a title similar to "8 ways time tells us NOT to use temporal tables".

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

Yes, I've updated more than I wanted to at times.

I like the general theme of continuous development. I believe that is true in all phases of our lives.

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 3

Wild. I hadn't even thought of temporal tables as an added protection for the data. Thanks for sharing the concept.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eric M Russell SSC Guru Points: 125562 More actions · Answer 4

...Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files...

Can deleted rows be recovered from a temporal versioned table?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

Eric M Russell (11/15/2016)
...Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files...
Can deleted rows be recovered from a temporal versioned table?

Interested in the answer to this (as a person who once accidentally executed the DELETE whilst somehow not including the WHERE clause that was sitting there right next to it :pinch:).

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

Gary Varga (11/15/2016)
Eric M Russell (11/15/2016)
...Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files...
Can deleted rows be recovered from a temporal versioned table?
Interested in the answer to this (as a person who once accidentally executed the DELETE whilst somehow not including the WHERE clause that was sitting there right next to it :pinch:).

This has happened to me before. What a difference a single @ makes... :crying:

@id = 123;

DELETE FROM MyTable WHERE @id = @id;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Rod at work SSC-Dedicated Points: 34015 More actions · Answer 7

Timely article, Steve. Something has happened here, which I'm not involved with, that really emphasizes what you've said. An apt word, what you've written here.

Kindest Regards, Rod Connect with me on LinkedIn.

Steve Jones - SSC Editor SSC Guru Points: 736418 More actions · Answer 8

Eric M Russell (11/15/2016)
...Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files...
Can deleted rows be recovered from a temporal versioned table?

Yes. They are in the history table

Eric M Russell SSC Guru Points: 125562 More actions · Answer 9

One useful new feature for SQL Server would be something like "declarative table level DML assertions". Basically this would be sort of like a trigger that intercepts a DML operation and first compares the estimated rows that would be affected to a DDL declared threshold, and if exceeded, would throw an error without actually applying the UPDATE or DELETE. However, that or course would depend on reliable statistics and cost estimator. Still useful, but less ideal, would be if it compared actual rows affected just prior to the commit, and then performing a rollback and error if needed. I'm sure this could be done today using a regular DML trigger, but it would be very inefficient.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gary Varga SSC Guru Points: 82166 More actions · Answer 10

Thanks for the clarification Steve.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!