How to roll back a TRUNCATE statement

TRUNCATE statement

TRUNCATE is classified as a DDL (Data Definition Language) statement. Although it is similar to the DELETE command, which is classified as a DML (Data Manipulation Language) statement, it works in a very different way.

Just as the DELETE command removes the rows one at a time, and it records every deleted row in the logs, the TRUNCATE command deletes the data by deallocating the data pages which hold the table data. The TRUNCATE command records only the page deallocations in the logs, which is why the DELETE command is slower than TRUNCATE.

We can understand the TRUNCATE command's behavior to be equivalent to the commands DROP TABLE and CREATE TABLE. The TRUNCATE command behaves differently for different DBMS. For now, we will see how it behaves in MySQL and MSSQL.

Note: You can learn about DDL and DML from this link.

How TRUNCATE works in MySQL

In MySQL, the TRUNCATE command can't be rolled back. That is because, in MySQL, TRUNCATE has an implicit commit. An example code is given below to show how TRUNCATE works in MySQL:

CREATE TABLE Books (
id INT,
name VARCHAR(50) NOT NULL,
genre VARCHAR(50) NOT NULL,
cost INT NOT NULL
);
INSERT INTO Books (id, name, genre, cost)
VALUES
(1, 'Book 1', 'Action', 1000),
(2, 'Book 2', 'Fantasy', 1000),
(3, 'Book 3', 'Horror', 1000),
(4, 'Book 4', 'Mystrey', 1000),
(5, 'Book 5', 'Thriller', 1500),
(6, 'Book 6', 'Action', 1500),
(7, 'Book 7', 'Horrow', 1500),
(8, 'Book 8', 'Thriller', 1500),
(9, 'Book 9', 'Action', 1500);
/*
START TRANSACTION;
INSERT INTO Books 
VALUES (20, 'Book15', 'Cat5', 2000);
ROLLBACK;
SELECT * FROM Books;
*/
START TRANSACTION;
TRUNCATE Books;
ROLLBACK;
INSERT INTO Books 
VALUES (20, 'Book', 'Romance', 3000);
SELECT * FROM Books;

Code example

Lines 1–6: A table Books is created.
Lines 8–18: Some dummy values are added to the table using the INSERT command.
Lines 20–26: A simple transaction that inserts a value in the table Books and then rolls back the transaction. In the end, the table is shown.
Lines 28–30: A simple transaction is done. In the transaction, first, the TRUNCATE command runs on the table Books. The transaction is rolled back.
Lines 32–34: A new row is inserted in the table Books, and then the table is shown.

In the example above, it can be seen that even after the transaction was rolled back, the effects of the TRUNCATE command still persist. This is because the TRUNCATE command has an implicit commit. There is another transaction that has currently been commented out, but if we run that instead of the other transaction, it can be seen how a normal rollback works in MySQL.

Note: The MySQL version being used here is 5.8. But the behavior for the TRUNCATE command stays the same for the later versions as well.

How TRUNCATE works in MSSQL

In MSSQL, TRUNCATE can be rolled back if it is in a transaction, as it does not have an implicit commit. An example code is given below to show how TRUNCATE works in MSSQL:

CREATE TABLE Books (
id INT,
name VARCHAR(50) NOT NULL,
genre VARCHAR(50) NOT NULL,
cost INT NOT NULL
);
INSERT INTO Books (id, name, genre, cost)
VALUES
(1, 'Book 1', 'Action', 1000),
(2, 'Book 2', 'Fantasy', 1000),
(3, 'Book 3', 'Horror', 1000),
(4, 'Book 4', 'Mystrey', 1000),
(5, 'Book 5', 'Thriller', 1500),
(6, 'Book 6', 'Action', 1500),
(7, 'Book 7', 'Horrow', 1500),
(8, 'Book 8', 'Thriller', 1500),
(9, 'Book 9', 'Action', 1500);
BEGIN TRANSACTION;
TRUNCATE TABLE Books;
ROLLBACK;
INSERT INTO Books 
VALUES (20, 'Book', 'Romance', 3000);
SELECT * FROM Books;

Lines 1–6: A table Books is created.
Lines 8–18: Some dummy values are added to the table using the INSERT command.
Lines 20–22: A simple transaction is done. In the transaction, first, the TRUNCATE command runs on the table Books. The transaction is rolled back.
Lines 24–26: A new row is inserted into the table Books, and then the table is shown.

In the above example, after the transaction is rolled back, our table data gets restored, and no loss occurs. This is because in MSSQL, if the TRUNCATE command is in a transaction, it can be rolled back.

Note: The MSSQL version being used here is 14.0.

How TRUNCATE works in PostgreSQL

In PostgreSQL, TRUNCATE statements cannot be directly rolled back. However, if a table is truncated within a transaction block and the transaction has not been committed, we retain the ability to roll back the entire transaction. This process effectively reverses the truncate operation and any other changes made within the same transaction.

CREATE TABLE Books (
id INT,
name VARCHAR(50) NOT NULL,
genre VARCHAR(50) NOT NULL,
cost INT NOT NULL
);
INSERT INTO Books (id, name, genre, cost)
VALUES
(1, 'Book 1', 'Action', 1000),
(2, 'Book 2', 'Fantasy', 1000),
(3, 'Book 3', 'Horror', 1000),
(4, 'Book 4', 'Mystrey', 1000),
(5, 'Book 5', 'Thriller', 1500),
(6, 'Book 6', 'Action', 1500),
(7, 'Book 7', 'Horrow', 1500),
(8, 'Book 8', 'Thriller', 1500),
(9, 'Book 9', 'Action', 1500);
BEGIN;
TRUNCATE TABLE Books;
ROLLBACK;
INSERT INTO Books 
VALUES (20, 'Book', 'Romance', 3000);
SELECT * FROM Books;

Recover data after using TRUNCATE

If we lose our data by using the TRUNCATE command, there are a few ways to recover our data which are as follows:

One of the ways the data can be recovered is through backup. So, if a full database backup exists, we can easily recover the data by overwriting the database with the backup.
Another way is that we recover our data using logs. Every database has logs so that in case of failures, the database can be recreated by using the logs. These logs store every transaction that occurs in a database. The TRUNCATE command deallocates pages and stores which pages were removed in logs. If those pages still exist in the MDF (Master Database File), we can read those pages and get our data back. But this is a very risky approach as the free space will be overwritten with new data.
The last way is to use a tool like SQL Database Recovery. This tool can recover truncated tables in some DBMS. More tools like this do exist and can recover most lost data.

Free AI Mock Interviews

Coding Interview

Coding PatternsFree Interview

Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.

System Design

You TubeFree Interview

Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources