Why Logshipping and Full/Diff backups work without breaking one anothe

[brage98]

I understand we can setup log-shippings and let it roll. While the log shipping is going on, we can make full-backup and Diff backups without breaking the logshipping?!! It's awesome and I love it.

I'm super curious to know why though?! From what I understand, when we make a full/Diff backup of a database, we need to use these full/diff backups before we can use any subsequent log-file (the log files simply keep track of what's been changed since the last backup/diff/tran backup) So, how does log shipping work even after I make a full backup and hide it somewhere the log-shipping job doesn't even know about?! how/why! this is magic (good kinda magic, but still i hate not knowing)

 

[mrdenny]

Here's how both diffs and log backup work.

Every time a page is changed a bit on the page is flipped. When a differential backup is made it simply grabs all the pages which have the bit flipped and backs them up. The transaction log isn't really touched for the most part. A mark is put in the log saying that a differential was taken, and the differential includes the log ID that marked the last last checkpoint.

When a transaction log is taken it only takes the records from the transaction log. It doesn't touch the pages that have already been changed.

When you restore the diff the database is set to the log ID that was taken at the last checkpoint. When you restore the logs you restore from that point on, which would be in the log backup taken directly after the differential was taken.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[brage98]

Thank you sooo much for the reply! So if I’m not mistaking, following scenario holds:

10:30 am: FULL
10:45 am: START LOG SHIPPING:
11:00 am: Log #1 = Change #1
11:15 am: Log #2 = Change #2
11:20am: DIFF BACKUP #1 = Change #1, #2
      Log is marked with the Diff Backup
      Diff has the ID of the last Log (11:15 am)
11:30am: Log #3 = Change #3
11:45am: Log #4 = Change #4

In log shipping @ 11:30
We have Changes #1 and #2 restored in the Secondary database;
The 11:20 Diff backup doesn’t change anything
Log #3 can be restored in our secondary database since it is up to date as of Change #2 (since the database is in read-only or standby mode)

If we were to do this manually though,
We could either restore through Full -> Log #1, Log #2, Log #3, Log #4
Or
Full -> Diff -> Log #3, Log #4

Makes perfect sense,
Thanks!

 

[mrdenny]

correct, you can use either restore method.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog