Recover Data with a SQL Server Temporal Table
Read time 7 minutes
In SQL Server 2016, Temporal Tables were introduced for historical data tracking. This article covers their setup, querying, and restoration methods. It emphasizes data protection, discusses corruption issues, and recommends Kernel for SQL Database Recovery, a robust tool for data recovery and backup.
In SQL Server 2016, Microsoft introduced ‘Temporal Tables,’ alternatively referred to as ‘System-Versioned Temporal Tables.’ This innovative feature provides native support for capturing historical data changes within tables, allowing users to retrieve information about stored data at any given point in time.
System-versioned temporal tables offer significant utility by providing comprehensive insights into both the data stored in the database and any modifications (such as updates or edits) made to the table. Unlike merely displaying the current data within the current timeframe, this feature captures and presents a detailed history of data changes.
In this article, we will guide you through Temporal Tables, also known as System-Versioned Temporal Tables, explaining how they function, how to query data from them, and most importantly, how to restore data using a SQL Server temporal table.
A Temporal Table, also known as a system-versioned temporal table, is a user-defined table specifically created to maintain a complete historical record of changes made to the data in a database. This historical log of data alterations enables administrators or database administrators (DBAs) to review it at any point in time.
A temporal table includes two columns, referred to as period columns, each defined with a datetime2 data type: SysStartTime and SysEndTime. These columns are designated as period columns and are used by the system to store a period of validity for each specific row that has been modified or undergone a single change.
In addition to the period columns, the temporal table includes a reference to a second table that mirrors the schema. This second table is commonly known as the ‘History Table.’ Users have the choice to either select an existing history table or let the system create a default history table.
The system-versioning for a table involves creating a pair of tables: one for the current data and another for the historical data. As previously mentioned, the period of validity for each specific row is defined using the two datetime2 data type period columns.
SysStartTime, also referred to as the period start column, stores the start time for each row, while SysEndTime, also known as the period end column, stores the end time for each row.
The following illustration illustrates that the current value for each specific row is stored in the current table, while the history table retains previous values for all rows, along with the corresponding period start and end times indicating their validity.
The example below demonstrates how to create a table, incorporating the usage of period columns to record the validity period for each row:
In the above scenario, a database table is established to store user information, utilizing period columns to capture the validity period by storing both the start and end times for each row. Moreover, you can execute INSERT, UPDATE, DELETE, and MERGE queries on this table.
The following example demonstrates how to retrieve temporal data from a SQL Server temporal table.
In the aforementioned query, we extracted SYSTEM_TIME data by specifying a specific date range based on the predefined UserID. The retrieved data was then sorted according to the ValidFrom period column.
The diagram below illustrates the workflow of a fundamental temporal data query.
.
Recovering data using a SQL Server temporal table involves pinpointing the data’s state at a specific time in the past. This process becomes invaluable, especially when there is a requirement to restore specific records or reconstruct an entire table.
Below is the step-by-step record recovery process to follow:
Review Changes Made to the Table
Once the query executes, you’ll be given with a time point informing if the record was deleted or a change was made to the table, now to recover the record that’s missing or changed proceed to the next step.
Create Query to Retrieve Record using AsOF
The below query is to retrieve the table record from a specific point of time by using the sub-clause AsOF.
Create Temp Table to Insert the Record
The below query inserts the data (table record) found deleted into the temp table after creating it.
Set Identity Insert ON and Insert the Deleted Record and Disable Identity Insert then
With the query given below, we’ve first enabled the identity insert for [dbo].[Users], as the table has an identity, and then, insert the deleted record in the table by fetching the record from temp table we created in the last step and disabled the identity insert by setting the status to OFF.
Verify that Record has been Restored
Now, it’s important that you verify that the deleted record has been restored successfully, to do that run the query given below.
Upon executing the above available above, you can verify that the deleted record is now restored and available again.
In summary, you’ve gained insights into the functionality of Temporal Tables, learned how to query temporal data, and discovered the process of retrieving deleted records using a Temporal Table.
Considering our focus on Temporal Tables and the effective restoration of missing or deleted records by analyzing past points in time using the history table, it is relevant to delve into the topic of SQL database corruption.
The prospect of irretrievably losing crucial data, be it a single database table, specific objects within a database, or sub-tables, is indeed alarming. Database file corruption, broken files, damaged data, and inaccessible files are among the numerous reasons that can lead to such disastrous situations.
In scenarios like so, we recommend using a SQL Recovery Software that helps you in getting the corrupt database file back to the normal state.
Along with repairing the damaged MDF/NDF database file(s), the software allows you to create database backups scripts once it is restored, generate live data preview, open and view MDF files without SQL Server, and more.
Kernel for SQL Database Recovery is an advanced and intelligent utility designed to assist users in various scenarios, including tasks such as SQL Server database backup, how to recover SQL database without backup and more.
