Backups and LSNs

Hi,
My question:
Is it possible to create the full backup so that taped copies can be
made and then rather than restoring the full backup on the target
machine, the incrementals resume and dovetail with the last
incremental prior to the full backup?
Current Scenario:
1. A sequence of incremental logs backed up and shipped to another
server (reporting) where they are restored (i.e. the target database
is in standby)
2. Each night the creation of incremental backups is suspended while a
full backup takes place. This full backup is also restored on the
other server.
3. Incrementals resume after a certain period of time which is
sufficient to allow the full backup to be restored.
4. The full backup is also copied to tape.
Motivation:
I want to avoid moving large backup files around on the network. I
want to decrease the time lag with the live data from the reporting
perspective. (I know about transactional replication and want/have to
stay with the current approach. Thanks)
Background:
There was an occasion when the full backup failed to move to the
target machine due to a space issue there. However, I noticed that the
incrementals post-(full backup) were still being copied across and
being restored even though the full backup didn't make it. This kind
of answers my question but the thing is that I was a bit confused at
the time and just wanted to get the problem sorted asap without doing
root cause analysis. Was it my imagination or it this possible?
I would have thought that the LSN count would have been reset by the
full backup. Here is a little pictorial of the sequence that I would
like.
Production Reporting
Box Box
... ...
i1 i1
i2 i2
i3 i3
fbup i4
i4 i5
i5 i6
... ...
Regards
Liam CaffreyLiam,
If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
when you perform a database backup. This allow you for instance to pick you second newest db backup
and restore it and then apply *all* subsequent log backups.
In other words, a database backup does not break the sequence of transaction log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060154.7bc1bebf@.posting.google.com...
> Hi,
> My question:
> Is it possible to create the full backup so that taped copies can be
> made and then rather than restoring the full backup on the target
> machine, the incrementals resume and dovetail with the last
> incremental prior to the full backup?
> Current Scenario:
> 1. A sequence of incremental logs backed up and shipped to another
> server (reporting) where they are restored (i.e. the target database
> is in standby)
> 2. Each night the creation of incremental backups is suspended while a
> full backup takes place. This full backup is also restored on the
> other server.
> 3. Incrementals resume after a certain period of time which is
> sufficient to allow the full backup to be restored.
> 4. The full backup is also copied to tape.
> Motivation:
> I want to avoid moving large backup files around on the network. I
> want to decrease the time lag with the live data from the reporting
> perspective. (I know about transactional replication and want/have to
> stay with the current approach. Thanks)
> Background:
> There was an occasion when the full backup failed to move to the
> target machine due to a space issue there. However, I noticed that the
> incrementals post-(full backup) were still being copied across and
> being restored even though the full backup didn't make it. This kind
> of answers my question but the thing is that I was a bit confused at
> the time and just wanted to get the problem sorted asap without doing
> root cause analysis. Was it my imagination or it this possible?
> I would have thought that the LSN count would have been reset by the
> full backup. Here is a little pictorial of the sequence that I would
> like.
> Production Reporting
> Box Box
> ... ...
> i1 i1
> i2 i2
> i3 i3
> fbup i4
> i4 i5
> i5 i6
> ... ...
>
> Regards
> Liam Caffrey|||Tibor,
Does this mean, for example, that in one scheduled job I can have
incremental backups running all day long at, say, 15 minute intervals,
and then a separate scheduled job can have a full backup running once
per night and that the two jobs won't interfere with each other (apart
from performance) as long as the transaction log is not truncated by
anything.
The full backup normally takes about 40 minutes to run.
If so, I could then run incrementals to the reporting box for weeks on
end or until the transaction log is truncated. Is this correct. Is it
a good idea to run log ships for so long. Any disadvantages?
Regards
Liam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> Liam,
> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
> when you perform a database backup. This allow you for instance to pick you second newest db backup
> and restore it and then apply *all* subsequent log backups.
> In other words, a database backup does not break the sequence of transaction log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct.
Correct.
> Any disadvantages?
Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
truncation in the production db for some reason...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.com...
> Tibor,
> Does this mean, for example, that in one scheduled job I can have
> incremental backups running all day long at, say, 15 minute intervals,
> and then a separate scheduled job can have a full backup running once
> per night and that the two jobs won't interfere with each other (apart
> from performance) as long as the transaction log is not truncated by
> anything.
> The full backup normally takes about 40 minutes to run.
> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct. Is it
> a good idea to run log ships for so long. Any disadvantages?
> Regards
> Liam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
>> Liam,
>> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the
>> tlog
>> when you perform a database backup. This allow you for instance to pick you second newest db
>> backup
>> and restore it and then apply *all* subsequent log backups.
>> In other words, a database backup does not break the sequence of transaction log backups.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>|||Interesting.
What happens if a full backup job is running and a separate
incremental log backup is kicked off during the middle of the full
backup? Or say SQLServer attempts to start a full backup just as an
incremental is running.
Will it gracefully prevent the incremental from running or will it go
ahead and create the incremental while the full is running? Are there
any issues with doing this? Is it better to create time windows for
the full backup.
Say then, in such a context, I wanted to restore a full backup plus
all subsequent incremental backups. How do I know which incremental to
apply after the full backup has been restored? The earliest? How can
the LSNs in the earliest post-(full backup) incremental file be
consistent with both the full backup file and the last pre-(full
backup) incremental file.
If you could refer me to some detailed info on this, I would
appreciate it. BOL doesn't seem to have anything in this direction.
Regards and thanks,
Liam Caffrey
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...
> > If so, I could then run incrementals to the reporting box for weeks on
> > end or until the transaction log is truncated. Is this correct.
> Correct.
>
> > Any disadvantages?
> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
> truncation in the production db for some reason...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.com...
> > Tibor,
> >
> > Does this mean, for example, that in one scheduled job I can have
> > incremental backups running all day long at, say, 15 minute intervals,
> > and then a separate scheduled job can have a full backup running once
> > per night and that the two jobs won't interfere with each other (apart
> > from performance) as long as the transaction log is not truncated by
> > anything.
> >
> > The full backup normally takes about 40 minutes to run.
> >
> > If so, I could then run incrementals to the reporting box for weeks on
> > end or until the transaction log is truncated. Is this correct. Is it
> > a good idea to run log ships for so long. Any disadvantages?
> >
> > Regards
> >
> > Liam
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> >> Liam,
> >>
> >> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the
> >> tlog
> >> when you perform a database backup. This allow you for instance to pick you second newest db
> >> backup
> >> and restore it and then apply *all* subsequent log backups.
> >>
> >> In other words, a database backup does not break the sequence of transaction log backups.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>|||Super-short background:
When backup starts, SQL Server does a checkpoint to record in the log the starting point for the
backup. Then it simply copies the pages to the storage media. At the end-time (this is what the
database will look like when you restore), the database backup also picks up the log pages produced
during the backup. This is so SQL Server can just apply all the pages and then re-do the
modifications that took place during the backup page copying. Possibly also a checkpoint is
performed at end of the backup process.
Check out RESTORE HEADERONLY and the four columns:
FirstLsn
LastLsn
CheckpintLsn
DifferentialBaseLsn
Lsn is "Log Sequence Number", each log record is given such a sequence number. My guess is that a
log backup "synchronizes" to the following log backup using the database backups CheckpointLsn.
Something like the log backups FirstLsn should match the database backup's CheckpointLsn. This is
only from looking at a couple of backups... I haven't found an elaboration of this topic, but to be
honest, I haven't looked for such either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410070334.7ab44ccf@.posting.google.com...
> Interesting.
> What happens if a full backup job is running and a separate
> incremental log backup is kicked off during the middle of the full
> backup? Or say SQLServer attempts to start a full backup just as an
> incremental is running.
> Will it gracefully prevent the incremental from running or will it go
> ahead and create the incremental while the full is running? Are there
> any issues with doing this? Is it better to create time windows for
> the full backup.
> Say then, in such a context, I wanted to restore a full backup plus
> all subsequent incremental backups. How do I know which incremental to
> apply after the full backup has been restored? The earliest? How can
> the LSNs in the earliest post-(full backup) incremental file be
> consistent with both the full backup file and the last pre-(full
> backup) incremental file.
> If you could refer me to some detailed info on this, I would
> appreciate it. BOL doesn't seem to have anything in this direction.
> Regards and thanks,
> Liam Caffrey
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...
>> > If so, I could then run incrementals to the reporting box for weeks on
>> > end or until the transaction log is truncated. Is this correct.
>> Correct.
>>
>> > Any disadvantages?
>> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some
>> log
>> truncation in the production db for some reason...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "LCaffrey" <lxc@.eosys.ie> wrote in message
>> news:4a6dded0.0410060629.48f1584f@.posting.google.com...
>> > Tibor,
>> >
>> > Does this mean, for example, that in one scheduled job I can have
>> > incremental backups running all day long at, say, 15 minute intervals,
>> > and then a separate scheduled job can have a full backup running once
>> > per night and that the two jobs won't interfere with each other (apart
>> > from performance) as long as the transaction log is not truncated by
>> > anything.
>> >
>> > The full backup normally takes about 40 minutes to run.
>> >
>> > If so, I could then run incrementals to the reporting box for weeks on
>> > end or until the transaction log is truncated. Is this correct. Is it
>> > a good idea to run log ships for so long. Any disadvantages?
>> >
>> > Regards
>> >
>> > Liam
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> > news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
>> >> Liam,
>> >>
>> >> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate
>> >> the
>> >> tlog
>> >> when you perform a database backup. This allow you for instance to pick you second newest db
>> >> backup
>> >> and restore it and then apply *all* subsequent log backups.
>> >>
>> >> In other words, a database backup does not break the sequence of transaction log backups.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>