By mistake update few data and how to rollback those changes later

SQL Server does not store that data after the transaction is "committed". So there generally no way in SQL Server to recover the previous version of the data.

The data may exist in the log file for a certain amount of time. However, this is not guaranteed and not a great way to recover data from prior transactions.

If you need a "history" of changes, you will need to implement temporal tables or CDC or some other method to capture the data BEFORE you have a problem.

If the database is in full recovery mode, then you can take a log backup and perform a point-in-time restore, but that will also undo wanted data changes.

Maybe you can also use the concept of “Temporal Tables”:

• https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
• https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios#repairing-row-level-data-corruption

Data modification statements are automatically committed by default. In order to undo changes, you'll need to start an explict transaction using BEGIN TRANSACTION, execute INSERT/UPDATE statements, and finally execute a COMMIT (to make changes permanent) or ROLLBACK (to undo uncommitted changes) as desired. One cannot undo data modifications once committed without restoring the database.

Adding to @Olaf Helper 's answer, you can recover data without overwriting other changes by restoring the database to a different name from a full backup, restoring log backups for point-in-time recovery (if possible), and then writing queries to salvage data from the restored copy.

Hi @Sudip Bhatt ,

Please refer some suggestions from below:

1. Create a trigger or use a tool like ApexSQL to record the transaction log of insert,update and delete actions. Then you could restore the few data manually.
2. Make sure the database where this table locates is in full recovery mode and take full,differential and log backups frequently. Then you could perform a point in time restore.
3. Based on 2, you could recover a single table from a SQL Server database backup.
4. Create another table as backup of this table and restore the data accordingly.
5. Add a transaction and try statement before and after the update statement.

       BEGIN TRY  
   BEGIN TRANSACTION  
           Select/update/delete                      
          COMMIT TRANSACTION   
   END TRY  
   BEGIN CATCH  
           ROLLBACK TRANSACTION  
           -- Consider logging the error and then re-raise  
           --select ERROR_MESSAGE()  
   END CATCH  
   

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
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.