1

For security reason I always took back up of my database. I took full backup in each month. And differential in each week. I used to save each back up file in different name. I lost my latest full back up file. But I have my latest differential back up file. So when I tried to restore my differential back up with the previous full back up , I am getting an error that

This differential backup cannot be restored because the database has not been restored to the correct earlier state

My scenario:

  1. Took Full Backup - File is with me
  2. Took Differential - File is with me
  3. Took Another full - Lost this file
  4. Differential file - is with me

So can I restore 4th one with 1st one. With out the 3rd ?

Is there any solution for this scenario? Can I restore it without losing data?

I am using SQL Server 2008 R2

Improve this question
4
  • 1
    Differential backup only relates to previous full backup you have taken so NO you cannot restore 4th differential backup if 3rd full backup is lost. Why cant you take transaction log backup ? What is recovery model of the database
    – Shanky
    Commented Mar 5, 2015 at 5:53
  • I am only a beginner. I am studying this. So I have no permission for transaction log back up. So I think there is no way to solve my issue isn't it? Recovery model is simple.
    – Anuj K.C.
    Commented Mar 5, 2015 at 6:15
  • 1
    If recovery model is simple there is no way
    – Shanky
    Commented Mar 5, 2015 at 6:20
  • [To understand] Whether there any way if it is not simple ?
    – Anuj K.C.
    Commented Mar 5, 2015 at 6:21

1 Answer 1

Reset to default
1

Since you said you are beginner and is in learning phase I would give you lot of good articles to learn about SQL Server backup.

To start with main question as already mentioned in my comment

So can I restore 4th one with 1st one. With out the 3rd ?

Differential backup only relates to previous full backup you have taken so NO you cannot restore 4th differential backup if 3rd full backup is lost. Also since as you said the database is in simple recovery model there is no chance of taking transaction log backup.

[To understand] Whether there any way if it is not simple ?

If your database were in full recovery you could have taken Transaction log backup. The good thing with transaction log backup is that Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.

So you could have restored first full backup and all log backups, in serial order, taken after the full backup. This would hold true even if 3rd full backup is lost.

You must read Understanding Backups in SQL Server there is a Backup myth buster series from PR related to backups you must also read that.

Feel free to ask if any doubts I would add the comments in this answer

Improve this answer
7
  • Thanks for your support. This is much helpful for my studies.It is my learning process so there would be more foolish questions . So have pardon me.
    – Anuj K.C.
    Commented Mar 5, 2015 at 7:09
  • I had a doubt, Does log table get overwrite after a while in simple recovery mode ? Does this affect in bulk or full recovery mode? Sorry for my language.
    – Anuj K.C.
    Commented Mar 12, 2015 at 4:40
  • There is nothing like log table. Can you refine your question please
    – Shanky
    Commented Mar 12, 2015 at 4:53
  • I thought that in transaction log backup it takes the backup from a log file. That is from the previous backup, It stores data in log file which is taken as new backup file. So only modified data would be there in new backup file. Am I correct sir ?
    – Anuj K.C.
    Commented Mar 12, 2015 at 5:18
  • Yes transaction log backup is backup of log file and each backup includes data which was changed after previous backup was taken. So you can say trn backup is incremental
    – Shanky
    Commented Mar 12, 2015 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.