Recovering a truncated table on a SQL Server

Tags:
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: 3  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SQL Server Ask the Experts
    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:
    report
  • Billylewis29
    You may repair sql server truncated table with the aid of utility below http://www.sql.repairtoolbox.com it must aid you
    10 pointsBadges:
    report
  • Brijesh
    Hi,

    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
    GO
    -- Create Test Table
    CREATE TABLE TruncateTest (ID INT)
    INSERT INTO TruncateTest (ID)
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    GO
    -- Check the data before truncate
    SELECT * FROM TruncateTest
    GO
    -- Begin Transaction
    BEGIN TRAN
    -- Truncate Table
    TRUNCATE TABLE TruncateTest
    GO
    -- Check the data after truncate
    SELECT * FROM TruncateTest
    GO
    -- Rollback Transaction
    ROLLBACK TRAN
    GO
    -- Check the data after Rollback
    SELECT * FROM TruncateTest
    GO
    -- Clean up
    DROP TABLE TruncateTest
    GO

    Hope this helps.
    8,745 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following