Geek Answers Handbook

How to recover data from a truncated table

going despite the question about sql server in mr book. shiv prashad koirala. I found out that even after using the truncate table command, the data can be restored.

please tell us how we can recover data when deleting data using the delete command and how to recover data if data is deleted using the truncate command.

what I know is that when we use the delete command to delete records, the record from it is done in the log file, but I don’t know how to recover the data, and when I read that the trimming table is not included in any record in the database log, how can this be restored.

if you can give me a good link to do it practically step by step than this will be a big help for me.

I have SQL Server 2008.

thanks

+7
sql-server sql-server-2008 truncate
source share
4 answers

If you use OPERATIONS in your code, TRUNCATE can be undone. If a transaction is not used and the TRUNCATE operation is committed, it cannot be retrieved from the log file. TRUNCATE is a DDL operation and is not logged in the log file.

DELETE and TRUNCATE can both be dropped in the TRANSACTION environment if the current session is not closed. If TRUNCATE is written in a query editor surrounded by a transaction, and if the session is closed, it cannot be undone, but DELETE can be dropped.

USE tempdb GO -- Create Test Table CREATE TABLE TruncateTest (ID INT) INSERT INTO TruncateTest (ID) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 GO -- Check the data before truncate SELECT * FROM TruncateTest GO -- Begin Transaction BEGIN TRAN -- Truncate Table TRUNCATE TABLE TruncateTest GO -- Check the data after truncate SELECT * FROM TruncateTest GO -- Rollback Transaction ROLLBACK TRAN GO -- Check the data after Rollback SELECT * FROM TruncateTest GO -- Clean up DROP TABLE TruncateTest GO
+5
source share

By default, neither of these two can be undone, but there are special cases where this is possible.

Truncate : When truncate is executed, SQL Server does not delete data, but only frees pages. This means that if you can still read these pages (using a request or a third-party tool), it is possible to recover data. However, you need to act quickly before these pages are overwritten.

Delete If the database is in full recovery mode, all transactions are logged in the transaction log. If you can read the transaction log, you can theoretically find out what the previous values ​​of all the affected rows were, and then restore the data.

Recovery Methods:

+5
source share

The SQL server saves the record (Page # and file #) of truncated records and these records, you can easily view from the query below. Once you get the page id and file id, you can put it on DBCC PAGE to get the full entry.

 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%'

Below is a link to an article explaining how to recover truncated records from an SQl server. http://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/

+4
source share

If your database is in full recovery mode, you can restore the data by truncating, deleting or deleting. Complete step by step. The article is here http://code.abhayprince.com/article/How-to-Recover-Data-from-Truncated -Deleted-or-Dropped-Table-in-SQL-Server-11

0
source share

More articles:


All Articles