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
Sir
This is very much use full and your demonstration technique is very good .
I like to study your all article and get more help from this.
Thanks.
Hi Pinal,
It is very useful.
Can you explain Internals of Differential Change Map?
Hi,
while setting up the log shipping i am receiving this below error message in sql 2008, i have 2 instances, 1 is default and the other is named on same machine.
[298] SQLServer Error: 53, Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 08001]
___________
another error:
165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
________
Message
[298] SQLServer Error: 53, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
could you please suggest ?
amit
this error is occuring while taking the transaction log backup on primary server….
Hi Pinal,
I am a regular reader of your articles and I really admire the amount of knowledge you have and dispense.
I do however agree with the guy who called you up as you mentioned that he said that “if he does not restore all the differential database backups and only restores log backups it just works fine.”
What you have explained in your article is a faster step to achieving the latest restore point, but it doesn’t conflict with his statement in any way.
The end result of the 2 procedures is the same, i.e:
His Way
1) Restore Full Backup >> Restore All Log Backups in proper sequence
Your Way (or better way)
2) Restore Full Backup >> Restore Last Diff. Backup >> Restore All Log Backups taken after the last Diff. backup
According to him this is an unnecessary step.
So why is it that what he said was, incorrect ?
It is an unncecessary step, only your way is presumably faster !
Please correct me if I have misunderstood.
Is there anything that can be achieved with a Diff. backup that cannot be achieved with Log backups ?
There is no better way – at the end your data should be in condition you need.
I like differential backup as this way, I do not have to do laborious task to restore each log backup since the last backup. If any way I can get the database up, I will do it.
Kind Regards,
Pinal
HI Pinal,
I agree with you.
In certain senario like,
full back up -every sunday
differential backup-for every day
T-log backup-for every 6hrs
if a disaster occurs on friday 1PM,then we first restore latest full backup,then,differential and Tlog backups follows.
“This is the standard we need to follow”.
Adnan it is not recomended to restore “full backup->T log ” in this way.we need to follow the sequences/standards.But the thing is it takes more time and if any t-log missed means LSN mismatch error occurs.
In the above example,if we take second approach means we need to restore the latest full back up /last sunday taken backup and from sunday to firday we need to restore SET OF T-LOG FILES and later TAIL LOG BACK UP (i.e..,friday 12PM-1pM).HENCE,it is risky and consumes more time which is not recomended.
Second apporach is applicable for only small application/personal use but,dont try in any organisation especially in banks
at the end of the day both will work.but still first approach is recomended
REGARDS
ADITHYA
Hi Adnan, I know its too old post but I want to clarify one thing,
I think that guy was wrong because of their following sentence (written in first paragraph of blog post)
“He replied that he starts with first full database backup and then sequentially all log backups and differential database backups.”
I this sentence you can see that he is right till all log backups but there after why he need to restore all differential database backups?
Thats why he is incorrect.
Correct me if I am wrong…
Nice and very much explanatory.
I think if someone reads your blog regularly he surely becomes dba very easily.
very nice
Thanks it is very helpfull , now it is very clear.
How do you know how often to do the backups? Everywhere I turn, I see different examples of how often to do fulls, diffs, logs, etc.
What’s the practice of determining how often to do them? With the power of modern servers, and the cheapness of disks, the cost is low compared to the benefit of having a recent backup. If your databases are not huge, why not just do an hourly full? That would make a restore super-simple.
You would use your recovery time (RTO) and recover point objectives (RP0) to work this out. More frequent backups if you can’t risk losing much data.
very nice
Thanks it is very helpfull , now it is very good.
excellent article……..
Hi Pinal
I have searched high and low but couldnt find anything.
Can you please help me with a restore database script from TSM; but via SQL Server agent job?
I manage to get it running for backups; but restores not working at all.
Any idea/advice?
Regards
how to get differential backups that has done from the past one year.
Hello Pinal Dave,
Please first understand my concept.In my windows application i want to restore Database from D:\\database.bak …After successful connection i want to do Entry using my .net application form…now Database with latest Entry should be saved at D:\\Database.bak without using Replace logic.can u help me..
Nice article sir…its too clear for me to restore the db..Thanks much
how full backup works , whts its impact and wht hapend to other tranjections when backup is going on
Hi Sir Nice aritical, Must be readen by every DBA
This article is really helpful for newbies like me.
Thanks for the article
Can you please suggest some article of your’s for Performance Tuning
Every time I post a question in google and see your name pop up as an option to get the answer, I always go to your blog. This explanation is very clear and has saved me a lot of time.
Thank You Kindly,
Marty
Nice Article. You explain it very precisely and clear. Thanks
Great article and easily understandable. Require some advice though. Right now I have a request to backup up a heavily used database with 2 full backups at 3:00 am and 4:00 pm with differentials every 15 minutes except between 2:00 am and 6:00 am (maintenance/open window). Shouldn’t I be taking backups of the log files? They seem to be growing very fast (Full recovery model). If so, when is the best time also bearing in mind the fastest and most efficient recovery process?
Thanks in advance
Toukey