If you have (system-versioned) temporal tables in your database and wrong information is entered into it, you may need to restore the last known good state. If you can afford to restore the entire database, that solves it. But if you want to restore just one table that is theoretically not possible: as it is discussed in the below thread and in the official documentation, “you can’t update PERIOD columns” (the validity start and end date columns).
Modify data in a system-versioned temporal table – SQL Server | Microsoft Learn
So my workaround is the following if you have the data available from a backup.
- Turn off the system versioning so that you can rename or delete the existing pair of tables (current and historical data).
- Create a new history table (the very same structure you had) and fill it up from your backup. You can have the period columns data taken over with no problem.
- Create a new current data table (the very same structure you had) and fill it up from your backup, with the exception of the two period columns which you will not be able to manually modify. Just let them be filled by the system.
- Load the current data in the history table as well! This is where the twist in the story begins.
- Update the validity end datetimes in your history table to be identical to the validity start datetimes of your current data table.
- Turn on system versioning.
The result is that you have your original history and current state of affairs in the tables back. It comes at the price that there is one extra record in the history table for each of your current records. Their beginning time stamp is the original validity start datetime and their ending timestamp is the time you did your repair. For any client applications though this is not likely to make the difference, for any given point in time they will see the expected data as before.