Truncate and Restores

Question

Post reply

Truncate and Restores

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
March 18, 2014 at 9:37 am

#305242

I am looking for a basic article that shows how truncate affects restores.
- a table
- a backup with data in the table.
- add some data
- a log backup.
- truncate the table
- a log backup
Show how recovery before and after the truncate works with restores of the full and logs, and what data is available. If you want to take a backup (full and/or log) while the truncate is in progress (uncommitted), that would be great.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

Hi Steve,

Are you asking for an article on Point-In-Time recovery or is there anything more than that?

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Truncate table doesn't affect restores in any way. What exactly are you looking for, proof that truncate table is logged (and hence is rolled forward by a log restore)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

GilaMonster (4/13/2014)
Truncate table doesn't affect restores in any way. What exactly are you looking for, proof that truncate table is logged (and hence is rolled forward by a log restore)?

yes

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

In that case, I'll take it. Won't be this month though. Is that OK?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 734418

April 16, 2014 at 1:53 pm

#1706571

That's fine. I know you're good for it.

Sometime.

Divine Flame SSCoach Points: 15943 More actions · Answer 6

Steve\Gail,

I have written something on it. I may submit it today. You can check if it is good to publish.

P.S.: Like everybody else on SSC, I would always be interested in reading a Gail Shaw article on this :-).

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Divine Flame (4/17/2014)
I have written something on it. I may submit it today.

Cool, I'll scratch it off my to-write list then.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Divine Flame (4/17/2014)
Steve\Gail,
I have written something on it. I may submit it today. You can check if it is good to publish.
P.S.: Like everybody else on SSC, I would always be interested in reading a Gail Shaw article on this :-).

Submit it and I'll take a look.