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. Let us learn about backup timeline.
In general, databases backup in full recovery mode is 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 a real story here. 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 a differential database is useful and many are not aware of the correct method to restore the full recovery model. Before I start explaining please understand the following diagram where I have demonstrated time line when a backup was taken.
Backup Timeline
Let us remember the golden rule for restoring 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 the 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 the database to the current state.
If you do not want to have a 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 the 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 the 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.
Let me know if my explanation is clear to you all. If there is any confusion regarding how full database backup, restore works, then do inform me.
Let us see how to take backup in very quick video:
Let us see how to restore backup in very quick video:
Reference : Pinal Dave (https://blog.sqlauthority.com)
67 Comments. Leave new
Thank you. Your article served to confirm my understanding. However, that understanding is precisely what has given me a scare today. Some of our staff manually take a full backup (to an arbitrary file on an arbitrary path) of a database from time to time, to restore on a development server for testing development etc. Our backup process is weekly full, daily diff, and hourly logs. My understanding leads me to conclude that since the manual full is “undocumented” and not stored with the routine backups, every time someone does this, we are left exposed to potential data loss, for up to week, until the next full backup is taken. Am I correct? For now I am stopping this process and requiring such copies to be retrieved from the off-site storage.
Hi Pinal,
Could you please explain how Log shipping works? Do we need to perform a restore of “Full backup of primary data center” on the secondary data center and then configure the log shipping? Or just by configuring “Log Shipping” the secondary data center gets the complete copy of the master database?
Picture speaks thousand words…The best way to understand the back up-restore.
Hi Pinal,
I am taking a full backup weekly and differential backup all the other days. I can see the details of the last Database backup and Last Database log backup information in the database properties section of the restoring (norecovery) database. Is there any way we can find information about when the last differential backup was restored ?
Hi
I have a doubt:
If I do backups as you have suggested and restored the database as described.
nth Transactional backup time: 3:45 AM
kth Differential backup time: 4:00 AM
(n+1)th Transactional backup time: 4:15 AM
Now after Full backup restore and kth Differential backup restore, I need to restore (n+1)th Transaction backup (right ?) to get back in working state. But this (n+1)th backup will contain data from 3:45 to 4:15 and we have already restored till 4:00 via kth differential backup.
Doesn’t it give error while restoring (n+1)th backup?
Hi Pinal/Dusht,
From your comment above, I have tried a similar situation.
I have taken a full database backup, then a transaction backup, and then a differential backup, and again a transaction backup.
I have successfully restored the full backup. After that when I am trying to restore the differential backup, its giving the following message as below.
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I can restore the transactional backups without any problem. But the differential backup following the transactional backup is not working.
Am I doing anything wrong ? Does it mean that the transactional backup will break the following differential backups.
I assume that the transactional backups needs to be taken as copy-only for not to break the differential backup chain.
Make sure to to restore your full backup with the no recovery option.
Nick – Thanks for your comment.
The error would mean that you are restoring a differential backup on top of a full backup which is not eligible for this differential backup. There is another full backup taken after the one which you restored.
Nice write up but I’d be very worried if a DBA on my team did not know this as it’s fairly fundamental stuff.
Thanks Nick. I am glad you liked it.
Hi Pinal – When I started with the Back up and Restore, I initially thought the multiple Differential back up have to be restored (after the Full Back up and before the Transaction log back up). But then, I realized that the latest Differential backup after the full back up is enough. But what would happen if we still restore the multiple Differential back ups after full backup? Will there be any data duplication or errors (due to primary key duplication).
Dusht,
U have asked very relevant question. I expected that someone has answered it. Here my explanation….
When Kth Differential executed at 4.00 AM, Log file flashed out its information into differential backup. So from 4.00 onward Log file contains active log transactions. Actually in 4.15 T log backup, it contains information from 4.00AM to 4.15AM. Hope this will help you.
Super fine Article
very nice artical
what happenes internally when we take a backup of a database
based on type of backup, the data is read from database and kept in backup file.
Excellent Topic @Pinal Dave