SQL server. Updated data is lost and reverted back to the past data

Question

SQL SERVER. I have a database that works fine and saves the update of the data and i see that data updated, but after a possible week or month, I find that the data returns to its original position before the modification and the modification completely disappears as if it was not done. How to resolve this problem?

Here is an example of how I update my data.

ALTER procedure [dbo].[PRC_Name_update]
(
@id int,
@DeezNutz nvarchar(50)
)
as
begin
update Person set
Name= @DeezNutz
where id=@id
end

After this problem, I added a trigger before update, and I store the log of updates in a separate table and this updates disappears from this table also

Answer 1

What you describe is not possible unless there is an open transaction which is being rolled back. That would indicate you have code somewhere which is using a transaction and not COMMITing it properly at the end.

Or the database was restored to a previous state.

Answer 2

Sounds like someone is restoring a backup of the database. You will need to check your environment.

Answer 3

Hi @basel khalil ,

Welcome to Microsoft Q&A!
Agree with them. Your situation sounds like a restore operation did be performed.
Please use this script to check the restore history:

SELECT    
  [restore_date]  
      ,[destination_database_name]  
      ,[user_name]  
      ,[backup_set_id]  
      ,[restore_type]  
      ,[replace]  
      ,[recovery]  
      ,[restart]  
  FROM [msdb].[dbo].[restorehistory]  

This script can be used to find the user who did the restore operation:

  SELECT [USER_NAME]   
      ,restore_date  
      ,destination_database_name  
FROM msdb..restorehistory  
WHERE destination_database_name = 'YourDatabase'  

For more information, please see: https://www.sqlshack.com/how-to-get-a-sql-database-restore-history/
Hope these could give you some help.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.