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.
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
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：
- The recovery model system is using
- 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 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.
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