Recovering a truncated table on a SQL Server

Tags:
SQL Server backup and restore
SQL Server recovery
SQL Server tables
SQL Server Truncated Table
How can I recover a truncated table on a SQL Server?
ASKED: January 21, 2009  5:47 PM
UPDATED: June 25, 2012  10:50 PM

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: 2  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

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