Posted by Hugo Kornelis on 11/18/05 01:30

On Thu, 17 Nov 2005 20:03:10 GMT, ronin 47th wrote:

>Hi group,
> In one of the books 'Gurus Guide to Transact SQL' i found this info:
>
>------------------------------------------------------------
>TRUNCATE TABLE empties a table without logging row deletions in the
>transaction log. It can't be used with
>tables referenced by FOREIGN KEY constraints, and it invalidates the
>transaction log for the entire database.
>Once the transaction log has been invalidated, it can't be backed up
>until the next full database backup.
>------------------------------------------------------------
>
> Does it mean that the log backup taken after table truncation is an
>invalid backup?
>
> Help me! i'm lost....
>
>Thanks in advance
>Ronin

Hi Ronin,

It's not invalid - but it's not useful anymore either.

If you use the full recovery model, a restore to a point in time
consists of two steps:
1. Restore a full backup to restore the database to the state it had
when that full backup was taken;
2. Restore one or more log backups to re-apply all changes that have
been made to the database after the full backup was taken, up to the
point in time where you want the restore to stop.

A TRUNCATE TABLE operation is minimally logged. That means that there is
just enough information in the log file to rollback if the transaction
fails or commit if it doesn't - but not enough to re-apply the changes.

Now, if one change can't be re-applied, you'll agree that it makes no
sense to go on re-applying changes that were done after that change. In
fact, it is very dangerous, since it might ruin your databases data
integrity.

As a result, when you execute TRUNCATE TABLE or other minimally-logged
operations, the effect will be that you can't restore to a point-in-time
between the moment the TRUNCATE TABLE was executed and the next full
backup.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]