Recover data from TRUNCATE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Recover data from TRUNCATE

12 posts, Page 1 of 2

1 2 »»

Recover data from TRUNCATE

Rate Topic

Display Mode

Topic Options

Author

Message

rithython

Posted Wednesday, March 09, 2011 12:06 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 10, 2011 4:28 AM
Points: 3, Visits: 6

Dear All,

Could any one help me to recover the data of a table which I already truncated?

Thank you in advance for your kind help.

Rithy

Post #1075778

GSquared

Posted Wednesday, March 09, 2011 12:08 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

You'll need to restore a backup from before the truncation, and pull the data out of that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1075782

Greg Charles

Posted Wednesday, March 09, 2011 12:12 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:34 AM
Points: 4,007, Visits: 4,857

Basically, you would restore a backup to a new database and copy the table and data back into the original database.

How you do the restore depends on the database's recovery model and what kind of backups you've made.

Greg

Post #1075784

rithython

Posted Wednesday, March 09, 2011 11:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 10, 2011 4:28 AM
Points: 3, Visits: 6

Hello All,

Thank you for your help. But, I got only the backup file in the old date before the TRUNCATE transaction. So, what should I do?

Thank you,

Post #1076017

mohammed moinudheen

Posted Wednesday, March 09, 2011 11:58 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:28 AM
Points: 2,170, Visits: 3,583

Is the database in SIMPLE recovery model?

Mohammed Moinudheen

Post #1076026

GilaMonster

Posted Thursday, March 10, 2011 12:59 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 37,739, Visits: 30,015

rithython (3/9/2011)

Thank you for your help. But, I got only the backup file in the old date before the TRUNCATE transaction. So, what should I do?

REstore it as a new database, copy the data over.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1076039

rithython

Posted Thursday, March 10, 2011 3:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 10, 2011 4:28 AM
Points: 3, Visits: 6

Now my database in the simple mode.The user use it as normal.But it don't have the old data because all data has been TRUNCATE.

So what should I do?

Thanks,

Rithy

Post #1076084

GilaMonster

Posted Thursday, March 10, 2011 3:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 37,739, Visits: 30,015

Get a backup from before the truncate ran. Restore it as a new database. Copy the data over into the table that was truncated.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1076086

homebrew01

Posted Thursday, March 10, 2011 4:06 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,554, Visits: 7,212

If your truncate happened on Tuesday at 2:00 pm, get the backup file closest to, but earlier than Tuesday 2:00 pm, for example Monday night at 11:00 pm.

If your database is named "MyDatabase", then restore the Monday 11:00 pm Full backup with the name "MyDatabase_Monday11pm", which makes another database. Then copy the table data from "MyDatabase_Monday" to "MyDatabase". After the data has been recovered, you can delete "MyDatabase_Monday11pm".

Since your database is SIMPLE recovery, if data was added to the table after Monday 11:00 pm, you can not recover that. If you want to be able to recover all the data next time, then your database needs to be in FULL recovery, and then you also take transaction log backups every 30 minutes or so.

Post #1076108

mimran18

Posted Sunday, April 08, 2012 3:11 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:35 AM
Points: 13, Visits: 251

Hi,

If your SQL Server is in full recovery model than you can recover it without backup.
SQL server keeps the entry (Page # & file #) of the truncated records and those records, you can easily browse from the below query. Once you get the page ID & file ID , you can put it in the DBCC PAGE to retreive the complete record.

SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID] ,[Slot ID],[AllocUnitId] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + 'dbo.Student' + '')) AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS') AND Description Like '%Deallocated%'.

Given below is the link of article that explains , how to recover truncated records from SQl server. http://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/

MI
http://raresql.com

Post #1279958

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions