Recover data from TRUNCATE

Question

Recover data from TRUNCATE

rithython

Old Hand

Points: 329
More actions
March 9, 2011 at 12:06 pm

#236543

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

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

rithython Old Hand Points: 329 More actions · Answer 3

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,

M&M SSC-Insane Points: 21700 More actions · Answer 4

M&M

SSC-Insane

Points: 21700

March 9, 2011 at 11:58 pm

#1296908

Is the database in SIMPLE recovery model?

M&M

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

rithython Old Hand Points: 329 More actions · Answer 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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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, MVP, M.Sc (Comp Sci)
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

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

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.

mimran18 SSC-Addicted Points: 451 More actions · Answer 9

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

Eric Hu²º¹² Mr or Mrs. 500 Points: 506 More actions · Answer 10

In the sqlserver Truncate or drop delete table data recovery methods:

1, if misoperation existed prior to a full database backup ( or have multiple differential backup or incremental backup), the first thing to do is to enter a log backup ( if in order to keep the log file larger and the log on TRUNC. Chkpt option for1that you finished. ) backup log dbName to disk = ' fileName'

2, restoring a full database backup, attention needs to use the with norecovery, if there are other differences or incremental backups, restore restore database dbName from is one by one disk = 'fileName' with norecovery

3, recovery of the last log backup is just do the log backup, specify the recovery time to misuse the moment before restore log dbName from disk = 'fileName ' with stopat = ' date_time' note: database recovery mode must be completely mode, and no log backup database cannot be truncated.

--The confidence is a premise, the courage is a motive, the perseverance is assurance!
http://www.cnblogs.com/huyong/[/url]

Eric Hu²º¹² Mr or Mrs. 500 Points: 506 More actions · Answer 11

Eric Hu²º¹² (4/9/2012)
In the sqlserver Truncate or drop delete table data recovery methods:
1, if misoperation existed prior to a full database backup ( or have multiple differential backup or incremental backup), the first thing to do is to enter a log backup ( if in order to keep the log file larger and the log on TRUNC. Chkpt option for1that you finished. ) backup log dbName to disk = ' fileName'
2, restoring a full database backup, attention needs to use the with norecovery, if there are other differences or incremental backups, restore restore database dbName from is one by one disk = 'fileName' with norecovery
3, recovery of the last log backup is just do the log backup, specify the recovery time to misuse the moment before restore log dbName from disk = 'fileName ' with stopat = ' date_time' note: database recovery mode must be completely mode, and no log backup database cannot be truncated.

--1

use master

backup database logTest

to disk='D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak'

with format

--2

use logTest

create table table_1(Id int, test varchar(max))

insert table_1(Id,test) values (1,'3333');

insert table_1(Id,test) values (2,'4333');

insert table_1(Id,test) values (3,'5333');

use master

--2.1

WAITFOR DELAY '00:00:05'

GO

use logTest

truncate table table_1

select GETDATE()

use master

--3

use master

backup log logTest

to disk='D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest_log.bak'

with format

--4

--DECLARE @dt datetime

--SELECT @dt=DATEADD(ms,-1800,GETDATE())

--select @dt

restore database logtest

from disk = 'D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak'

WITH REPLACE

RESTORE LOG logTest

FROM DISK='D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest_log.bak'

with STOPAT= '2009-10-26 12:38:57.133'

GO

--The confidence is a premise, the courage is a motive, the perseverance is assurance!
http://www.cnblogs.com/huyong/[/url]