On the updated version of SQL i.e., SQL server 2016 Microsoft launched the Temporal Tables which is also known as System-Versioned Temporal Tables. It is a built-in feature that supports the gathering of information about the data stored inside the tables. Temporal tables are very useful as they provide deep information about the data files and any change which is reflected on the table than just showing the current data in the present time slot. So, in this technical guide, you will get to know about what is Temporal files and the ways to retrieve data files from the SQL Server temp table. So, don’t miss any step and follow us line-by-line and continue your reading.
What is a Temp table in SQL server?
A temp table or Temporal Table is a user table that is designed to store a full history of changes reflected on the data in the database and this record of change in data allows inspection by the admin side or the DBA at any time.
A Temporal Table consists of two columns and each of them is defined with a datetime2 data type – SysStartTime & SysEndTime and describes this as a period column. To store a period of validity the system uses the two table columns for every specific row which either is modified or has undergone through a single change.
Restore Data with SQL Server Temp Table
To store the data with the help of the SQL server is possible by finding the state of data at any particular instance of time. Recovering the data files or records is very helpful especially for the case when you need to recover select or reconstruction of an entire table surfaces.
Given below is the steps you need to follow to recovery:
Review changes Made to the Table
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate=’2020-01-10 01:00:00’
SET @EndDate=’2020-01-13 22:45:59’
SELECT * FROM [Users] FOR system_time between @StartDate and @EndDate
WHERE Name = ‘Pollard’
Once your query was executed then you’ll be given with a time point informing if the record was changed or deleted. Now process to recover the record that was missing or changed proceed to the next step.
Create a query to Retrieve a Record using AsOF
SELECT CDC_DemoID, name, Address, City
FROM [Users] FOR SYSTEM_TIME AS OF ‘2020-13-01 10:20:00’
WHERE Name=’Pollard’
The above query is used to retrieve the table record from a specific point of time by using the sub-clause AsOF.
Create a Temp Table to Insert the Record
INSERT INTO #TempTable (CDC_DemoID, name, Address, City)
SELECT CDC_DemoID, name, Address, City
FROM [Users] FOR SYSTEM_TIME AS OF ‘2020-13-01 10:20:00’
WHERE Name=’Pollard’
The above query is to insert the table record found that was found deleted into the temp table after creating it.
Set the identity Insert ON and insert the Deleted Record and Disable Identity Insert then
SET IDENTITY_INSERT [Users] ON
INSERT INTO [Users] (CDC_DemoID, name, Address, City)
SELECT CDC_DemoID, name, Address, City
FROM #TempTable
SET IDENTITY_INSERT [Users] OFF
With the help of the above query, we have first enabled the identity insert for [dbo].[Users], as the table has an identity and then inserts the deleted record in the table by fetching it from the temp table we created and finally disabled the identity insert by setting the status to OFF.
Verify the Restored Record
It is important to verify whether the restored records have been recorded successfully or not.
SELECT * FROM [Users] WHERE Name=’Pollard
By using the query given above, you can verify that the deleted record is now restored and available again for future use.
Last Words
In this technical guide, you will get to know about the brief knowledge of what is temp table in the SQL server and how to retrieve data from the SQL server temp table. Although these methods are quite long so, to skip all these steps you can take the help of the SQL Database Recovery tool by which you can easily retrieve the data files from the SQL server temp table.
