October 21, 2011 at 11:08 am
Hello,
I have some questions about restoring backups and log shipping that have me confused.
From my understanding , unless I use a copy- only backup, when I restore my backup files, I have to do them in the correct sequence number. For some reason I thought I could skip some. For example if I do a full backup and I'm doing transaction log backups every 15 minutes, and I do a one time differential backup and then deleted that diff backups, could I then just restore the full backup and all the tranaction log backups in order -- will that work.
What has me confused is how does log shipping work if all backups must be in order if there are full backups done while logshipping is turned on? So if you do Full, tran, tran, Full, tran, tran, all the log shipping see is the tran log backups, so if was to do a restore using those files, could I skip the 2nd Full backup.
Thanks if anyone can help clear this up for me.
October 21, 2011 at 11:22 am
Full backups don't impact log backups.
So if I do
- a full backup 1
- run transaction a
- log backup with transaction a
- run transaction b
- log backup with transaction b
- run transaction c
- full backup 2 (may or may not contain transaction c. depends when the commit occurred.
- run transaction d
- log backup with transaction c and d
You should always keep log backups since the earliest full backup you have. SQL Server will replay the LSNs in order and if there are dups (in a full and a log backup), it ignores one. However if you lose full backup 2 above, you still have all the transactions with the 3rd log backup and can restore:
- Full backup 1
- Log backup 1
- log backup 2
- log backup 3
That's how log shipping works.
Same with Diffs. They do not include the log records. Fulls and Diffs essentially include the data only (not completely, but for a high level view, they do). The log backups include changes.
October 21, 2011 at 12:12 pm
Thanks Steve,
First, it is an honor to have you respond to my post 🙂
I understand that tran log backups can be restored in order skipping over any intermediate full or diff backups. But you can't do that with diffs, right.
If I have a simple recovery db, and i do full, diff, diff, full, diff.... I have to have both fulls to recover the to the last diff.
October 21, 2011 at 12:23 pm
No, just the last full. So with full1, diff1a, diff1b, full2, diff2a, diff2b, you'd restore full2 then diff2b. You can't restore full1 then either of the diff2 as full backups reset the differential base (unless taken with copy_only)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2011 at 12:50 pm
Gail beat me to the answer, but think of it like this. A full backup is a base starting point, always, for differentials. The diffs are linked to the last full backup (non copy only) made before them. It doesn't matter if I make 1 diff or 25 diffs, they are all linked to the full backup, and I only need the last diff backup.
Logs are separate, and they flow between and through the full backups. However since a log is only changes, I have to use at least one full backup to get to a starting point. From there, as long as I have consecutive log backups, I can use those to restore through and past any subsequent full backups, without needing those later full backups.
October 21, 2011 at 1:05 pm
Thanks Gail (another honor)
I think I got it. I can't do a diff If I don't have the full that it was created from.
Cooley high.
Just some background on my questioning... we have some huge databases that are in simple recovery becuase most transactions are loaded once a week, but we have a DR server a few states away with no direct network to that DR site(i.e., slow internet connection).
So at this point our options are :
mirroring (but we have stand ed. , so only sych. mode only)
log shipping (if we change to full mode)
or some type of manual backup recovery method, which is why I was asking about what backups were needed to restore the logs/diffs.
thanks again.
October 21, 2011 at 1:27 pm
nawillia (10/21/2011)
Thanks Gail (another honor)I think I got it. I can't do a diff If I don't have the full that it was created from.
Oh you can take a diff backup if you've lost/deleted the full it's based on. SQL doesn't check that the back file exists. The diff will be completely and totally useless, but you can take it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2011 at 8:31 pm
Interesting discussion.
Would the Full-Diff-Log backup method be recommended over the Full-Log method or it depends on the size of the database? I'm asking because I have seen both methods like the examples below:
Method #1
Full backup - once a day
Diff backup - every X hour(s)
Log backup - every XX minute(s)
Method #2
Full backup - once a day
Log backup - every XX minute(s)
But then #2 would make the restore process awkward for the dba because he'd have to restore every log after the last full backup, right?
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 22, 2011 at 4:52 am
Depends on the size of the database, maintenance window length, restore requirements and a few other things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2011 at 12:49 pm
GilaMonster (10/22/2011)
Depends on the size of the database, maintenance window length, restore requirements and a few other things.
I see what you mean.
Every situation is unique and must be analyzed properly. There is no easy recipe. 🙂
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply