SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

« SQL SERVER – BLOB – Pointer to Image, Image in Database, FILESTREAM Storage

SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY »

SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

July 14, 2009 by pinaldave

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 restore full 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.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology | 38 Comments

38 Responses

on July 14, 2009 at 11:00 am | Reply Kuldip

Nice article Sir, Now it’s very much clear about Differential Database Backup
- on July 14, 2009 at 11:56 am | Reply pinaldave
  
  Kuldip,
  
  It is great that you liked it!
  
  Regards,
  Pinal
on July 14, 2009 at 12:46 pm | Reply sqldude

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
- on July 14, 2009 at 1:17 pm | Reply pinaldave
  
  Thanks sqldude,
  
  Fixed the typo.
on July 14, 2009 at 7:08 pm | Reply Ramdas

Good Illustration and Nice explanation of the diff. Database backup concept.
on July 15, 2009 at 5:22 am | Reply Vishal

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
on July 15, 2009 at 7:42 am | Reply Sunit

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.
on July 15, 2009 at 10:20 am | Reply Ganesh

Hi Pinal,

Exact and perfect explanation.

with best wishes
Ganesh
on July 15, 2009 at 11:29 am | Reply Guru

Hi Pinal,

Nice Article!!! Thanks. It will be very useful for Beginner’s.

Good Work!!!
on July 15, 2009 at 12:47 pm | Reply Beginner

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
on July 15, 2009 at 4:59 pm | Reply prasad

hi,
just i learned SQL DBA can u give best guidence to me

Thnaks Pinal
on July 16, 2009 at 12:40 am | Reply Edwin

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
on July 21, 2009 at 5:31 pm | Reply pramod

Thx Pinal. It’s really good article.
It is Helpful for the novice sql guys.
on July 23, 2009 at 1:05 am | Reply Dave

If you cannot understand this concept move away from the server.
on July 23, 2009 at 10:04 am | Reply Imran Mohammed

@ (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
on August 19, 2009 at 8:44 pm | Reply Rick Martinez

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.
on September 8, 2009 at 1:30 pm | Reply Richard

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.
on September 16, 2009 at 8:02 am | Reply Ravi

Dear Pinal, Good work but y dont you have Related Posts in your Blog? I think it would be really useful
on November 9, 2009 at 11:02 pm | Reply fbit_user

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 :(

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/33a6e516-c7f7-4acd-a95c-7a1a84e41ff8
on November 20, 2009 at 5:36 am | Reply Madhuri

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.
- on January 29, 2010 at 7:27 pm | Reply Peter
  
  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..
  
  http://www.sqlskills.com/BLOGS/PAUL/post/BACKUP-WITH-COPY_ONLY-how-to-avoid-breaking-the-backup-chain.aspx
on February 3, 2010 at 6:39 pm | Reply Vivek

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.
on March 16, 2010 at 12:20 am | Reply Edwin

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
on March 21, 2010 at 7:01 am | Reply SQL SERVER – Fix : Error : 3117 : The log or differential backup cannot be restored because no files are ready to rollforward Journey to SQL Authority with Pinal Dave

[...] I suggest you all to read one more post written by me earlier. In this post, I explained the time line with image and graphic SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model. [...]
on March 27, 2010 at 4:53 pm | Reply Raghvendra Rai

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.
on August 5, 2010 at 6:41 am | Reply Chalapathi Pinisetty

Hi Pinal,

It is very useful.
Can you explain Internals of Differential Change Map?
on September 7, 2010 at 11:26 pm | Reply amit kumar

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
on September 7, 2010 at 11:27 pm | Reply amit kumar

this error is occuring while taking the transaction log backup on primary server….
on September 8, 2010 at 5:07 pm | Reply Adnan

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 ?
- on September 8, 2010 at 5:12 pm | Reply pinaldave
  
  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
on September 13, 2010 at 3:54 pm | Reply Snehal

Nice and very much explanatory.

I think if someone reads your blog regularly he surely becomes dba very easily.
on January 1, 2011 at 5:33 pm | Reply zakaria sahafi

very nice
Thanks it is very helpfull , now it is very clear.
on March 15, 2011 at 7:15 pm | Reply Stephen

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.
on April 27, 2011 at 2:13 pm | Reply prashant mishra

very nice
Thanks it is very helpfull , now it is very good.
on July 12, 2011 at 7:03 am | Reply SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

[...] Backup Timeline and Understanding of Database Restore Process in Full Recovery Model [...]
on August 12, 2011 at 9:30 pm | Reply rajani

excellent article……..
on October 25, 2011 at 5:16 pm | Reply Brigette

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
on January 23, 2012 at 7:01 am | Reply SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31 « SQL Server Journey with SQL Authority

[...] Server Interview Questions and Answers ISBN: 1466405643 Page#161 Backup Timeline and Understanding of Database Restore Process in Full Recovery Model Effect of Compressed Backup Setting at Server Level on Database Backup Target Recovery Time of a [...]