ROLLBACK TRUNCATE in SQL Server

Have you ever accidentally executed the TRUNCATE command on a wrong table? This will lead to all data loss. The worst thing is that you will not have a chance to get your data back. In this article, we will have a look how to avoid such situations and have a chance to ROLLBACK TRUNCATE.

You cannot ROLLBACK TRUNCATE

Simply, you cannot rollback a transaction if it is already committed but you can do something else to get the data back (or at least some parts of it).

When you execute the TRUNCATE statement, your data is still in the MDF file. However, it is not visible because SQL Server is treating this as free space (TRUNCATE is telling SQL Server to deallocate data pages).

The only way to get the data back is to somehow read deallocated data pages and convert them into readable data.

You must act fast because free space will be overwritten with new data if not already. If you can stop your SQL Server instance and make a copy of MDF and LDF files that would buy you more time.

There are some tools that may do this kind of restore.

You can ROLLBACK TRUNCATE

TRUNCATE is a logged operation, but SQL Server doesn’t log every single row as it TRUNCATEs the table. SQL Server only logs the fact that the TRUNCATE operation happened. It also logs the information about the pages and extents that were deallocated. However, there’s enough information to roll back, by just re-allocating those pages. A log  backup only needs the information that the TRUNCATE TABLE occurred. To restore the TRUNCATE TABLE, the operation is just reapplied. The data involved is not needed during RESTORE (like it would be for a true ‘minimally logged’ operation like a BULK INSERT).

SQL Server knows what pages belonged to the table as far as they are locked with an exclusive lock, and just like all X locks, they are held until the end of the transaction. That is why pages or extents can’t be deallocated, and certainly, cannot be reused.

Here is an example:

We got a count of 504 rows and a number of pages. Now we’ll look at the count of rows, and the pages that belong to the table.

BEGIN TRAN
TRUNCATE TABLE dbo.Products;
SELECT COUNT(*) FROM dbo.Products;
 
DBCC IND('AdventureWorks', 'Products', -1);
DBCC EXTENTINFO('AdventureWorks', 'Products', -1);
 
SELECT resource_type, resource_description,
        request_mode FROM sys.dm_tran_locks
WHERE  resource_type IN ('EXTENT', 'PAGE')
AND   resource_database_id = DB_ID('AdventureWorks');

You will see no rows from DBCC IND, and 0 rows from count(*). The locks info returns the following:

resource_type resource_description request_mode
————- ——————– ————
EXTENT        1:33352              X
PAGE          1:42486              X
EXTENT        1:42488              X
PAGE          1:42487              X
PAGE          1:42488              X
PAGE          1:42489              X
PAGE          1:23027              X
PAGE          1:23030              X
PAGE          1:23029              X
PAGE          1:26992              X
PAGE          1:26993              X

The extent and page locks include all the pages that we saw in the DBCC IND output. Only after you ROLLBACK the transaction will the locks be released, and you should see all the rows and pages back on the table again.

ROLLBACK TRAN;
GO
SELECT COUNT(*) FROM dbo.Products;
DBCC IND('AdventureWorks', 'Products', -1);
GO

Be careful and always wrap the TRUNCATE table statement in the transaction.