Another feature I wish SQL Server had: as of timestamp

If you are using Oracle 10g or greater, you can query the database tables as of a certain time using their Flashback technology. This is great if you had some data deleted and you do not want or cannot restore a full backup to retrieve the lost data.

Below is an email no DBA wants to receive. One of the users by mistake deleted all of the data out of a table.

Now what? If you are using Oracle, you can restore last nights RMAN backup and apply the archived redo logs on a test server and then copy the data out. You can also use DataPump or Export and just pull out the 1 table that you need if you have those backups. You still need to find the backups and try and remember the commands to restore the data.

An easier way if the deletion happened recently (within a few hours), is to query the data with an additional clause.

First let us query the table and see if the data is really gone. Yes it is 😦

Now we add the “as of timestamp” clause at the end of the statement. I asked the user what time the data was deleted and they said around 9:35am or so. It is a static table so none of the data would have changed that morning so I query the data as of 9:30am and the query now brings back all of the rows.

select * From XAPP2REF
as of TIMESTAMP TO_TIMESTAMP('2022-08-22 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Great! now we can recover the data without having to go through an Oracle restore.

There are many ways to get the data back into the table. This is the way I did it.

First, I loaded the data into a temporary table called XAPP2REF_jim as a safety net.

create table XAPP2REF_jim as
select * From XAPP2REF
as of TIMESTAMP TO_TIMESTAMP('2022-08-22 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Then I copied the data out of the temporary table we just created and into the XAPP2REF table that was missing the data. Don’t forget to COMMIT the data!

insert into XAPP2REF select * From XAPP2REF_Jim

Now the user can query the table and all of the data is there!

Knowing all of the features that the different database platforms have is a lot of work, but every platform is different and there are plenty of timesaving tips you can learn by READING THE DOCUMENTATION! To read more about Oracle’s Flashback technology follow this link: https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01004

If I would have done a DataPump restore or even a full RMAN restore, it may have taken 30-180+ minutes depending on the size of your database. Using the “as of timestamp” method took approx. 2 minutes and the users could continue on with their work with little downtime.