I assume you all know that there are three types of Database Backup Models, so we will not discuss on this commonly known topic today. In fact, we will just talk about how to restore database that is in full recovery model.
In general, databases backup in full recovery mode are taken in three different kinds of database files.
- Full Database Backup
- Differential Database Backup
- Log Backup
What really perplexes most people is differential database backup.
Let me narrate here a real story. One of the DBAs in local city once called me up and laughingly said that he has just found something not smart about SQL Server Product Team in terms of database backup. I just could not believe this negative remark against SQL Server Product Team so I asked him to explain me what is it. He told me that in his opinion it is an extra step when it is about Differential Database backup. I asked him how he restores his database. He replied that he starts with first full database backup and then sequentially all log backups and differential database backups. He continued his explanation and said that he has figured something interesting; that is, if he does not restore all the differential database backups and only restores log backups it just works fine. According to him this is an unnecessary step.
Well, I am extremely happy to say he is wrong. He has totally failed to understand the concept of differential database backup. I called up another friend in his company and told him this story and he found it funny too! He suggested that he will explain to my friend that he needs to do all differential backups first and then all log backups after the last differential backup. I was again amazed and didn’t know what to do. He was wrong too!
After interacting with many DBAs I have realized that it is quite confusing to most of the people how differential database is useful and many are not aware of the correct method to restorefull recovery model. Before I start explaining please understand the following diagram where I have demonstrated time line when a backup was taken.
Let us remember the golden rule for restore first.
‘After restoring full database backup, restore latest differential database backup and all the transaction log backup after that to get database to current state.’
From the above-listed rule, it is very clear that there is no need to restore all the differential database backups when restoring databases. You can only restore the latest Differential database backup. Differential database backup is the backup of all the changes made in database from last full backup; it is cumulative itself. That is the reason why the size of next differential database backup is much more than the previous differential database backup. All differential database backups contain all the data of previous differential database backups. You just have to restore the latest differential database backup and right after that install all the transaction database backups to bring database to the current state.
If you do not want to have differential database backup and have all the transaction log backups, in that case, you will have to install all the transactional database backups, which will be very time consuming and is not recommended when disastrous situation is there and getting server back online is the priority. In this way, differential database backups are very useful to save time as well as are very convenient to restore database. Instead of restoring many transaction database logs, which needs to be done very carefully without missing a single transaction in between, this is very convenient.
In our example, there are multiple paths to get server to the current state.
Path 1 (SLOWEST) : Full Database Restore >> Log Backup 0_1 >> Log Backup 1_1 to all remaining logs.
Path 2 : Full Database Restore >> Differential Database Backup 1 >> Log Backup 1_1 to all remaining logs.
Path 3 (FASTEST) : Full Database Restore >> Differential Database Backup 2 >> Log Backup 2_1 to all remaining logs.
No comments:
Post a Comment