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

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.

  1. Full Database Backup
  2. Differential Database Backup
  3. 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.

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 (http://blog.SQLAuthority.com)

About these ads

61 thoughts on “SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

  1. 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

  2. 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

  3. 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.

  4. 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

  5. @ (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

  6. 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.

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

  8. 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

  9. 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..

      http://www.sqlskills.com/BLOGS/PAUL/post/BACKUP-WITH-COPY_ONLY-how-to-avoid-breaking-the-backup-chain.aspx

  10. 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.

  11. 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

  12. Pingback: 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

  13. 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.

  14. 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

  15. 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…

  16. 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.

  17. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  18. 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

  19. Pingback: SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31 « SQL Server Journey with SQL Authority

  20. 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..

  21. Pingback: SQL SERVER – Recover the Accidentally Renamed Table « SQL Server Journey with SQL Authority

  22. 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

  23. 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

  24. Pingback: SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video « SQL Server Journey with SQL Authority

  25. Pingback: SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video « SQL Server Journey with SQL Authority

  26. 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

  27. 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.

  28. 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?

  29. Pingback: SQL SERVER – Database in RESTORING State for Long Time | Journey to SQL Authority with Pinal Dave

  30. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  31. 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 ?

  32. 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?

  33. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s