x

Can I query data as of a point in time?

Does anyone know if SQL Server has a way to query data as of a point in time similar to Oracle's flashback query? If you aren't familiar with it, flashback query ability allows you to execute a normal select statement with an additional AS OF clause before the where clause to indicate a point in time. This functionality utilizes undo retention and data is only available based on the settings of the undo retention and tablespace restrictions.

Example of Oracle's flashback query:

SELECT * FROM MYTABLE AS OF TIMESTAMP (SYSDATE - INTERVAL '30' MINUTE);

more ▼

asked Jul 10, 2015 at 01:39 AM in Default

avatar image

thesqlguyatl
20 2 2 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The only way I can think of doing this in SQL Server is by restoring the database to that point in time, and then querying. Not really the answer you're looking for...

If you want to do this, though, when doing the RESTORE, make sure that you use the STOPAT parameter when doing the RESTORE LOGs. For examples of this, see the documentation here:

https://msdn.microsoft.com/en-us/library/ms186858.aspx#restoring_to_pit_using_STOPAT

more ▼

answered Jul 10, 2015 at 08:16 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x461
x442
x161

asked: Jul 10, 2015 at 01:39 AM

Seen: 1203 times

Last Updated: Jul 10, 2015 at 08:16 AM

Copyright 2018 Redgate Software. Privacy Policy