SQLTeam.com | Weblogs | Forums

Recovering a truncated table on a SQL Server


#1

How can I recover a truncated table on a SQL Server?


#2

Restore the database from backup.

You can restore to a new, temporary, database name and then "copy over" the data in the truncated table

If your database is in FULL Recovery Model you can restore from Log Backups using STOP AT command, with a date/time immediately prior to when the TRUNCATE happened, in order to ensure that all records in the truncate table are as-they-were when the Truncate occurred

In future running the truncate inside a BEGIN TRANSACTION block would allow use of ROLLBACK to cancel the operation - if the problem is discovered before using the COMMIT command.


#3

Thanks for the help.


#4

The new data will be deleted if recovering from backup.


#5

Yes, but you could restore an old Backup to a new, temporary, databases and then "copy" data from one to the other - so you could put newer data from Live into TempRestored, or you could copy "Missing" data from TempRestored into Live

INSERT INTO MyLiveDB.dbo.MyTable
SELECT *
FROM TempRestored.dbo.MyTable

If rows are duplicated in both you need some sort of NOT EXISTS to only copy "missing" rows.

If that table has an IDENTITY you need some extra steps to "preserve" the Identity Value Column during the INSERT