2

Is it possible to create a named point in the transaction history of the database, such that a subsequent connection could retrieve data at that named point in the log?

For example, lets say there is a loader process that is writing to multiple tables fairly often. At some point, a reader process creates a named point in the log, lets call it point A. The loader does some more updates and now we are at point B. Then another reader process comes along, and can select data as point of A.

Is such a thing possible in SQL server?

Unfortunately, the tables / loader doesn't have a bi-temporal structure, so the reader can't supply an as of datetime as a predicate on the select statements.

2

You can use a Log Mark to enable a restore to specific logical point-in-time of a database, but you can't use that in queries to the main database.

You can take a Database Snapshot to present a point-in-time consistent copy-on-write snapshot of an existing database.

|improve this answer|||||
  • That was exactly what I was looking. Unfortunately snapshots are not enabled on the deployed version of our SQL Server instances :-( However, I can build my own version by copying the required data into some tables within a single transaction. – ThatDataGuy Dec 21 '18 at 16:14
0

If you are able to define at which time you want the data to be returned, you could also query data from a specific time in the past by leveraging temporal tables (available from sql 2016 and later versions).

SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.