2 Fatal Considerations for a Point-in-time Restore in SQL Server Database

In this post, users will get to know various considerations that are important for performing a point-in-time restore. The considerations are related to type of recovery model and the information contained in the log chain.

Introduction

There can be times when an administrator wants to restore the database back to a previous point from the past. The restore can be a part of SQL recovery process. Reasons for this can be many. But, mostly it happens due to malicious DDL or DML statement or due to some accidental/malicious statements.

Point-in-time Restore

Point-in Time Restore

It may not always be possible to get back to a previous point in time. It’s because this process depends on two important factors.  They are:

  1. The recovery model system is using
  2. Your log chain

1.Recovery Model Considerations

It’s a common knowledge that there are primarily three types of recovery models namely; Simple, Bulk-Logged, and Full. The decision regarding which recovery model you choose is extremely important. This decision will impact the amount of data you can afford to lose in the event of a disaster.

Simple recovery model – Opting for this model implies that the user is ready to lose all the changes that were made in the database after last backup. Loss of data is the downside of this model. However, the upside to this model is the fact that it demands very little administration. The admin doesn’t have to worry about log backups or log chains. While this arrangement is not best for critical database, it does have several advantages to offer.

Bulk-Logged Recovery ModelBulk logged model – It enables users to carry out bulk operation without have to write every transaction in the log. It also improves bulk operation performance. The downside to this model is that you cannot perform a point-in-time restore since logs do not maintain records of each and every transaction.

Full recovery model – Ideally, to perform a point-in-time restore, database must use this model. As the name implies, each and every event/transaction that happened in the database gets recorded in the log. This enables the user to restore the database back to a point in the past. However, it’s not enough to just follow a full recovery model. In addition to that, you must also have complete log chain. Otherwise performing the restore will not be possible.

2.Log Chain Considerations

It starts when the database has been fully backed up and is also in full recovery model. It means that between two consecutive back-ups, every single operation will be recorded in full, differential, or log backup. That makes it possible to restore the system back to a point since all the log entries are available.

All you need is a log chain that is not broken. The chain usually gets broken by switching between two recovery models or rolling between them. So, in order to take the database back to a point in time; make sure the log chain is unbroken. Also, taking full backups between log back-up will not cause the chain to break. However, in case you do break the chain, it is possible to restart it by creating new differential or full backups.

Author Introduction:

Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com

One response to “2 Fatal Considerations for a Point-in-time Restore in SQL Server Database”

  1. Thank you for taking the time to post an article on such an important subject. I appreciate it.

    You say in your article that “The chain usually gets broken by switching between two recovery models or rolling between them.” Just to clarify, switching from FULL to BULK-LOGGED does NOT break the log chain nor does it, by itself, make it impossible to do a PIT restore unless a “Minimally Logged” operation takes place. Only those log files that contain a “Minimally Logged” operation CAN’T be used as a mid-log end point for a PIT restore. You either have to stop the restore to the point just before the log file backup was created OR use the whole log file backup to continue on to a different PIT. That’s why it’s a good idea to do a log file backup just prior to and immediately after any MINIMALLY LOGGED operation.

    To wit, you could stay in the BULK-LOGGED Recovery Model all day and still do PIT backups/restores because it’s not the disqualifier. MINIMALLY LOGGED operations are.