There is a lot of information in the world that people just assume is true because it has been repeated so much. Twinkies have an unlimited shelf life.  McDonalds hamburgers have so many preservatives, they will never get moldy. The problem is this information is frequently wrong. But the incorrect information is so widespread and so ingrained in our heads, it’s sometimes hard to correct your thinking.

The use of TRUNCATE TABLE and how it affects the transaction log is one such piece of information in the world of SQL Server. The common perception is that a TRUNCATE TABLE statement is a non-logged operation and that once it has been issued, the transaction log backup containing that statement cannot be recovered to a point in time after that. A new log backup must be made to be able to recover to a point in time. I think most DBAs today realize TRUNCATE is not a non-logged operation –  it is a minimally logged operation. It can still be rolled back and Paul Randal gives a couple of nice demonstrations showing this. But many people with years of experience still think that once a TRUNCATE statement has been issued, you can no longer restore a log backup containing that statement to a point in time after that command. I will admit, I was one of these people.

A little while ago, I was working with a developer on a stored procedure and noticed that he had used a TRUNCATE TABLE statement. Out of habit, I mentioned that, because the table was only ever going to have a small amount of data, he should use a DELETE statement instead. (TRUNCATE TABLE statements are usually used when clearing out a  table that contains thousands or millions of rows of data because it is much faster than a DELETE statement for deleting large numbers of rows.) He stated he used TRUNCATE because he didn’t care if the data could not be recovered in a disaster. It was transient data that could be recreated. I repeated to him the commonly held belief that if he used the TRUNCATE statement, then we would lose the ability to perform point in time restore until the next log backup happened. I decided to perform an experiment to illustrate this. It was then that I realized this was not true. A careful internet search revealed that yes, this is in fact, not true, even though many people think it is. (It doesn’t help that even some published works perpetuate this misconception.)

So in an attempt to provide yet another voice in the wilderness trying to kill this myth, here is a sample showing that you can, in fact, recover a transaction log to a point in time after a TRUNCATE TABLE statement has been issued.

First, let’s make a database and set it to full recovery mode.

CREATE DATABASE TruncateTest;
GO
ALTER DATABASE [TruncateTest] SET RECOVERY FULL ;
GO

Now, let’s make two tables to work with. Once they are created, we’ll take a full backup so we are not operating in pseudo-simple recovery mode and we can establish the log backup chain.

USE TruncateTest;

CREATE TABLE [dbo].[TableA] ([ColA] [int] NULL)
ON     [PRIMARY];

CREATE TABLE [dbo].[TableB] ([ColA] [int] NULL)
ON     [PRIMARY];

GO

-- Create full backup

BACKUP DATABASE [TruncateTest]
TO  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTest.bak'
GO

Now we’ll insert 10 rows into each table so we have something to work with.

DECLARE @loop TINYINT;
SET @loop = 1;
WHILE @loop <= 10
      BEGIN
            INSERT  INTO TableA
                    (ColA)
            VALUES  (@loop);

            INSERT  INTO TableB
                    (ColA)
            VALUES  (@loop);

            SET @loop = @loop + 1;
      END

Now we’ll insert an additional 5 rows into TableA. We’ll do this with a named and marked transaction so we can explicitly specify this point during a transaction log restore. Then, let’s truncate the table.

BEGIN TRANSACTION Transaction1 WITH MARK 'BeforeTruncateOfTableA'

DECLARE @loop TINYINT;
SET @loop = 11;
WHILE @loop <= 15
      BEGIN
            INSERT  INTO TableA
                    (ColA)
            VALUES  (@loop);

            SET @loop = @loop + 1;
      END

COMMIT TRANSACTION Transaction1

/* Now truncate TableA */

TRUNCATE TABLE TABLEA

Now we’ll insert an additional 5 rows into TableB, also using a named transaction.

BEGIN TRANSACTION Transaction2 WITH MARK 'AfterTruncateOfTableA'

DECLARE @loop TINYINT;
SET @loop = 11;
WHILE @loop <= 15
      BEGIN
            INSERT  INTO TableB
                    (ColA)
            VALUES  (@loop);

            SET @loop = @loop + 1;
      END

COMMIT TRANSACTION Transaction2

And finally, we’ll take a log backup.

BACKUP LOG [TruncateTest]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTestLog.bak'
WITH INIT
GO

At this point, we have a log backup that contains two explicitly marked points – one before a table was truncated and one after. Let’s see if we can restore to either one. First, restore to before the truncation.

/* First, restore the full backup */

USE master;

RESTORE DATABASE [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTest.bak'
WITH  NORECOVERY, REPLACE;

/* Now, let's restore to the point just before the truncate table, just to show
it can be done. Note: STOPAT uses the transaction NAME, not the text specified
in the WITH MARK clause. That is only a description. */

RESTORE LOG [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTestLog.bak'
WITH  STOPATMARK = 'Transaction1',
RECOVERY;

GO

At this point, TableA should have 15 rows and TableB should have 10 rows. Do they?

Now let’s do it again, but stop at a point in time after the TRUNCATE statement.

USE master;

RESTORE DATABASE [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTest.bak'
WITH  NORECOVERY, REPLACE;
GO

RESTORE LOG [TruncateTest]
FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DEMO2012\MSSQL\Backup\TruncateTestLog.bak'
WITH  STOPATMARK = 'Transaction2',
RECOVERY;

The RESTORE completes successfully. That’s our first indication this will work. What about the data? At this point, TableA should have zero records and TableB should have 15. Do they?

Yup.

So clearly, using a TRUNCATE TABLE statement does not prevent you from performing a point in time recovery!