\nThis is to see if someone deleted the data on purpose or by accident, so we can track down the individual and take preventative action. We received a few requests from customers looking for this information.
\nWhen we know the exact time of the data purge operation then we can easily recover the data with a stop-at clause during the log backup operation.<\/p>\n
Summary of the problem<\/strong><\/p>\n The following sections describe four key principles of GitOps:<\/p>\n Between 5 p.m. and 7 p.m. on January 5, 2020, the “dump truncate” table was truncated and the data from the “dump delete” table was removed from the “truncate test” DB. The probable questions that need to be addressed include:<\/p>\n <\/p>\n Prerequisites:<\/strong><\/p>\n Approach at a high level<\/strong><\/p>\n <\/p>\n Determine which log backup holds the delete and truncate operations:<\/strong><\/p>\n NOTE: We performed these steps by creating a new copy of the DB.<\/strong><\/p>\n When we restored the log backup from 6 PM, the “dump truncate” table was empty, and entries from the “dump delete” table were missing. As a result, it denotes:<\/p>\n Between 5:45 PM and 6 PM, the “dump truncate” table was truncated, and data from the “dump delete” table was wiped simultaneously.<\/p>\n You need to run the following restore commands:<\/p>\n Identify details of truncate and delete operations using log backup:<\/strong><\/p>\n Step 1: Collect transaction IDs for all truncate and delete operations that occurred between 5:45 and 6 p.m.<\/p>\n Query: <\/strong><\/p>\n We discovered that two operations, delete and truncate, were executed at 5:50 PM, and that these operations were performed by login RP Dev.<\/p>\n Step 2: Find the table names that are associated to the transaction id.<\/p>\n To get information on the delete operation, follow the steps below<\/em><\/p>\n I) Determine the delete operation’s object ID and partition ID.<\/p>\n <\/p>\n From this output, we can deduce the following information: II) Locate a table that contains the object ID and partition ID. <\/strong><\/p>\n Query:<\/p>\n <\/p>\n To get information on the truncate operation, follow the steps below:<\/strong><\/p>\n I) Determine the Truncate operation’s object ID and partition ID<\/em><\/p>\n OUTPUT<\/strong><\/p>\n Now we can deduce that data from “dump delete” was removed by RP DEV user at 5:50 PM under the transaction ID ‘0000:00016a96’ and that a total of 7 rows were deleted.<\/p>\n To get information on the truncate operation, follow the steps below: <\/strong><\/p>\n I) <\/strong>Determine the Truncate operation’s object ID and partition ID.<\/em><\/p>\n Query:<\/strong><\/p>\n Output<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n \nThe output of the truncate operation differs slightly from that of the delete operation.<\/p>\n Partition ID column:<\/strong> It is not displaying the correct partition ID. You can find this information in the description column. Partition ID is highlighted. Partition IDs are 72057594043564032 and 72057594043629568<\/p>\n Lock Description: <\/strong>Always SCH_M_OBJECT row <\/em>in Lock Description shows the correct Object ID. Object ID is: 885578193<\/p>\n Locate a table that contains the object ID and partition ID <\/p>\n Query: <\/strong><\/p>\n Output<\/strong><\/p>\n We can now establish that data from “dump truncate” was truncated at 5:50 PM by RP_DEV user under the transaction ID’0000:00016a95′.<\/p>\n Conclusion<\/strong><\/p>\n It is useful to know who performed the truncate and delete operation on data to avoid this from happening again when auditing is not already enabled. \n
\n
\n
\n
![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture1.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture2.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture3_0.png\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture4.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture5_0.png\")
<\/p>\n
\nDescription and Transaction Name columns: Delete operation was performed<\/strong><\/em>
\n <\/p>\n\n
![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture6.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture7_0.png\")
<\/p>\n
\nNow we can deduce that data from “dump delete” was removed by RP DEV user at 5:50 PM under the transaction ID ‘0000:00016a96’ and that a total of 7 rows were deleted.<\/p>\n![\"MSSQL8\"](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture8.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture9.png\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture10.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture11.png\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture12.PNG\")
<\/p>\n![\"MSSQL](\"\/wp-content\/uploads\/2026\/blog_article_images\/26213-body-Picture13.png\")
<\/p>\n
\nIn case of a delete operation, the business will have an idea of how many rows are removed.
\nTo have the exact time of recovery will be very helpful in recovering the data.
\nFurther third-party tools like ApexSQL Log and ApexSQL Recover can also be used to recover the data. <\/p>\n