Recovering a truncated table on a SQL Server

SQL Server backup
SQL Server recovery
SQL Server tables
How can I recover a truncated table on a SQL Server?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I do not think you can unless you have a good backup of that table. Once you execute a truncate then the server has no log of that event. All of that data is gone. Sorry and I hope that you have a good backup in order to restore from.

Truncate is a logged event. All statements which change data within the database are logged. If it wasn’t logged in wouldn’t pass the ACID test. Please stop telling people that truncate isn’t logged.

The truncate statement is minimally logged. This means that instead of logging that each row in the table was deleted, a mark is put into the log saying that all records in the table were deleted.

In order to restore you will need to restore your database and store the restore at the point when the truncate statement was run.

If you do not have log backups restore your most recent full, and differential (if you have diffs). That’ll be as good as it gets.

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Colin Smith
    I apologize for my phrasing. Mr. Denny is of course correct in saying that it is a minimally logged event. That being said the outcome is the same. Since the event is minimally logged you can not rollback the truncate action. Restore is your only option. Thanks for the correction Mr. Denny.
    0 pointsBadges:
  • Billylewis29
    You may repair sql server truncated table with the aid of utility below it must aid you
    10 pointsBadges:
  • Brijesh

    If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

    DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed.

    USE tempdb
    -- Create Test Table
    CREATE TABLE TruncateTest (ID INT)
    INSERT INTO TruncateTest (ID)
    SELECT 1
    SELECT 2
    SELECT 3
    -- Check the data before truncate
    SELECT * FROM TruncateTest
    -- Begin Transaction
    -- Truncate Table
    TRUNCATE TABLE TruncateTest
    -- Check the data after truncate
    SELECT * FROM TruncateTest
    -- Rollback Transaction
    -- Check the data after Rollback
    SELECT * FROM TruncateTest
    -- Clean up
    DROP TABLE TruncateTest

    Hope this helps.
    14,450 pointsBadges:
  • edwinjhoffer
    I don't think you can recover your truncated table if it is not performed within a transaction but you can see your truncated data using some undocumented functions like fn_dblog.
    3,740 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: