Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Recover data from TRUNCATE Expand / Collapse
Author
Message
Posted Wednesday, March 09, 2011 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 09, 2011 12:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:34 PM
Points: 15,442, Visits: 9,588
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
Posted Wednesday, March 09, 2011 12:12 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 4,056, Visits: 5,180
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
Posted Wednesday, March 09, 2011 11:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 09, 2011 11:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:49 PM
Points: 2,263, Visits: 3,758
Is the database in SIMPLE recovery model?

Mohammed Moinudheen
Post #1076026
Posted Thursday, March 10, 2011 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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
Posted Thursday, March 10, 2011 3:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, March 10, 2011 3:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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
Posted Thursday, March 10, 2011 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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
Posted Sunday, April 08, 2012 3:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 12:32 AM
Points: 13, Visits: 259
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 »

Add to briefcase 12»»

Permissions Expand / Collapse