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
Nice article Sir, Now it’s very much clear about Differential Database Backup
hi sir,
i want full sql and psql material clearly explan data please send me
Nice and clear explaination.
only one typo i guess
“Path 3 (FASTEST) : Full Database Restore >> Differential Database Backup 3 >> Log Backup 2_1 to all remaining logs.”
it should be
“Path 3 (FASTEST) : Full Database Restore >> Differential Database Backup 2 >> Log Backup 2_1 to all remaining logs.”
-Harshal
Good Illustration and Nice explanation of the diff. Database backup concept.
Well written article Pinal.
I have also found that this is a very common misunderstanding for many DBA’s about differential backups.
It would be great, if you could write an article showing steps and screen shots to restore using these backups.
Regards
Vishal
Nice article. Just nit-picking, but it would be nice to mention the easily forgotten “tail-backup” of the transaction log that needs to be done before starting the restore. Also all, except the last log backup, should be restored using the WITHOUT RECOVERY clause.
Hi Pinal,
Exact and perfect explanation.
with best wishes
Ganesh
Hi Pinal,
Nice Article!!! Thanks. It will be very useful for Beginner’s.
Good Work!!!
Really nice.
I can understand it now but I haven’t tried it.
Vishal suggest about an article showing steps and screen shots to restore using these backups. It would be really helpful for us.
Thanks Pinal
hi,
just i learned SQL DBA can u give best guidence to me
Thnaks Pinal
Sir,
It’s very a good and nice article on MS SQL Server database restore.
It clear up and add valuable knowledge to me.
Thanks,
Edwin
Thx Pinal. It’s really good article.
It is Helpful for the novice sql guys.
If you cannot understand this concept move away from the server.
@ (Duplicate) Dave.
Is that recommendation from Microsoft or you just making it up.
No one knows everything. Its good to learn before you do something wrong.
If you know everything, please share you knowledge, We definitely want to learn from experienced people like you.
~ IM
We are doing log shipping, and we are only shipping transaction logs from the primary database to the secondary database we do not ship the differential. We then restore the transaction log on the secondary database. Are we doing this wrong are we suppose to restore differential backups on the secondary database?
Our database is 250GB, we do a full backup on Wednesday, differential backup once a day (Sun, Mon, Tues, Thurs, Fri, Sat), and transaction logs every 30 minutes.
Good Day Mr. Pinal,
Excellent article, very informative and persuasive.
I’m a little confused with Transaction Logs.
Are they incremental in the sense that Log Backup2_2 contains only the data modifications after Log Backup2_1?
Thank you.
Dear Pinal, Good work but y dont you have Related Posts in your Blog? I think it would be really useful
hello sir,
my live (production) database (very few people use it) of size 5 GB (has got 7 years data) and we scheduled each night 1 differential backup and weekly 1 full backup. “No other backups are scheduled (as per my DBA’s decision)” As we know, hardly this system will have 100-200 inserts/updates/deletes per day (not more than that) but if I see size of differentials backed up database size is 1GB per day. Is there a way I can reduce as my boss asking why is this so huge in size when very few operations carried out daily?? As i am a developer I have very little knowledge on dba concepts like backups/restores. Please advise asap on this.
I reported same at microsoft site as well but changing recovery model to simple did nt help me :(
Pinal,
Thanks for the article. I am new to SQL Server and am trying to do a Restore of the database.
I have 1. Full Backup
2. Diff Backup 1,2,3,4
3. Transactional Backup 1,2,3,4,5
I could restore from the full backup.
And then (as per your article) tried to restore from
the latest diff backup (diff backup 4)
It errors out saying
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
SQL Server version is 2005. And the first diff backup is the larger than subsequent diff backups.
Am I missing something?
Please comment.
Hi Madhuri,
I guess you allready have cleard this issue you have, but just for others to read about it here´s an explanation.
You had a broken backup chain, explanation:
Let´s say you are using some 3´rd party tool like IBM TSM or Backup exec to do nightly backups, and you also do manual backups within your SQL server, the TSm one starts a full backup @ 22:00 and the SQL internal starts a Full backup @ 23:00
The problem is that the Full backup made by TSM/BE will be the 1rst full backup for TSM/BE, ´but since you have a Internal SQL backup dump this will render the next Diff backup done by TSM/BE unusuable since the diff between the First Full and next Diff will be the Diff between the Last full (SQL internal) and the Diff backup running in TSM/BE..
Which makes the last Full backup in TSM/BE totally unsuable.. Did anyone understand me?
Anyway here´s a blog entry that explains it much better..
https://www.sqlskills.com/blogs/paul/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain/
Hi
this is good illustration for some of the SQL DBA professionals who have misunderstandings about differnetial backups.
Nice article Pinal way to go.
This is For Rick Martinez,
AM i right to understand that you guys have configured log shipping,If yes then there’s no need to ship the differential on the Secondary database .it will automatically apply the logs on the Secondary warm StandBy database.
Dear Sir,
I have setup my Microsoft SQL Server database backup on the following schedule:
– Full Backup run on Sunday.
-Different backup run on Everyday at 11:00 PM
– Transaction log backup run on Every 4 hours everyday.
The backup was stored in second server’s hard drive (2 TB).
Questions:
1.Should I overwrite the backup file or append?
2.What is the best practice to implement Microsoft SQL Server database backup?
3.In the production servers, how is the DBA implement and setup the database backup?
Many thanks,
Edwin