Please can I have a technical meaning of point in time recovery? Does it mean that given a backup file, we must be able to recover upto any point in time during the span of the time covered by the backup?

If so, then is it right to say only a transaction log backup will allow for this? Or is only full or differential backup sufficient? How about recovery models - is full recovery model a must? Or can it be bulk recovery model also?

  • Do you mean RTO? (Recovery time objective) – George.Palacios Oct 15 '18 at 9:29
  • I read this concept in many books they say full recovery model supports point in time recovery – variable Oct 15 '18 at 9:31

Point in time recovery is the concept that a particular set of data can be restored to an exact point in time, rather than just to the time of the last backup file.

In this case with SQL Server, log backups are the usual mechanism for accomplishing this to ensure database consistency.

FULL or BULK recovery modes must be utilised to allow for point in time restore, with the BULK option minimizing the amount of log space needed for certain operations (CREATE INDEX, SELECT INTO, BULK INSERT...). If any of these mechanisms are used, the ability to restore to a point in time using a log backup is dropped, forcing you to restore to before / after the bulk logged operation, for that individual log backup. For more information on this see SQL Server Recovery Models. The reason for this is that the bulk-logged model utilising minimal-logging will not log the data pages to the log file during bulk-logged operations - it will only log the extents that have been changed. Whether you want to use BULK recovery model or not will depend on your individual circumstances and whether you can afford to drop the point in time restores in favour of potentially faster bulk logged operations.

Other than that, FULL recovery model will support your use case.

  • Are you sure that if bulk mode is used then the ability to restore point in time is lost? Because - in bulk logged model - transaction log backups rely on the database’s data files that were modified by the minimally logged operations being available during the backup operation. – variable Oct 15 '18 at 10:05
  • @variable it is lost only for those individual log backups where the BULK operation took place. All other log backups act the same as FULL recovery and allow for point in time restore. – George.Palacios Oct 15 '18 at 10:10
  • OK so a bulk transaction is minimally-logged as one whole set and assuming we have all backups, recovery can only be done either before this transaction started or after this transaction finished. Recovery is not possible at any point during this transaction - is my understanding correct? – variable Oct 15 '18 at 10:27
  • @variable correct - it affects the log backup file itself. – George.Palacios Oct 15 '18 at 10:28
  • Hi George please note that ability of point in time recovery is not lost for those individual log backups where the BULK operation took place. This is because as soon as you do thepg backup, the db system reads the data file and fills the transaciton log so it will be exactly like the full model. Bulk model only minimally logs the transactions in the log. But as soon as you do a transaciton log backup then it enabkes point in time recovery. Please let me knw if this is clear. – variable Oct 15 '18 at 17:04

PITR is the ability to recover a database to a particular state at some time in the past - ideally to as near to when a failure started to occur as possible - usual scenario. You may want to have a view of the state of the db at certain times in the past, but there are better ways of doing this than performing (time consuming) restores from backups and logs!

Various systems have various ways (and names) for doing this.

Oracle has redo logs - your system crashes, so you go to your most recent backup and apply the redo logs to the point where they are still valid - ideally merely seconds before any failure. Ideally, your redo logs are not kept on the DB server but copied out to redundant storage somewhere - even off site.

Oracle also has the concept of Flashback queries where you can query the database as if it were some time in the past (also known as "AS OF" or "ASOF" queries - Oracle also calls it "Total Recall") when your system hasn't gone down, but for some reason you wish to know the state of play at some time in the past - there is considerable overhead associated with this, so care is required in making use of this feature.

SQL Server calls it Point In Time Restore (1, 2), but does not have Flashback queries, but does have snapshots. However it also has features which allow the user to do more or less the same thing using what Microsoft call (System Versioned) Temporal Tables - these permit "AS OF" queries as explained here - see this answer for more details. See also this answer in the same thread re. Change Data Capture and Change Tracking (more lightweight)!

An interesting take on this Flashback/ASOF query is to be found in the Datomic system - an immutable database (seemingly a contradiction in terms) - check out "asof" and "since" here.

There are two key concepts in recovery.

  1. RPO - Recovery Point Objective: how many hours/minutes of data loss is acceptable?

  2. RTO - Recovery Time Objective: how much time will it take to recover after business process interruptions?

A good explanation of these two ideas is to be found here. But, ye get nuttin for nuttin! The more stringent your requirements, the more money you're going to have to shell out for equipment and software!

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.