Recover data from TRUNCATE

  • 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

  • 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

  • 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

  • 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,

  • Is the database in SIMPLE recovery model?

    M&M

  • 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
  • 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

  • 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
  • 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.

  • 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

  • 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²º¹² (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]

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

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