Restoring multiple transaction logs from a single file

Hi all,
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall
> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and based the result does
several RESTORE LOG commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegro ups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>

Restoring multiple transaction logs from a single file

Hi all,
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and
based the result does
several RESTORE LOG commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegroups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>

Restoring multiple transaction logs from a single file

Hi all,
I have a home-grown log shipping setup at work which I'm trying to
modify. At present if the standby server cannot apply the logs before
the live server writes a new set (usually because the db cannot be
locked), it fails and we have to do a full resync between the servers.
I've put in a mechanism whereby the status of the last attempt to apply
the logs to the standby database is recorded. If the primary sees that
the last attempt was successful it does a BACKUP LOG ... WITH INIT.
If it sees the last attempt was not successful it does the same but
WITH NOINIT, which I gather appends the new logs onto the end of the
current lot. This way the transaction logs will queue up every hour
until I can solve whatever the issue between the servers is so they can
be applied, thus saving a full 4 hour resync on a 220GB database.
Problem is this. For example, the secondary can't apply logs because
it can't get exclusive lock on the db. It marks that database as out
of sync in a table. Next time the primary backs up logs it sees the
secondary is not up to date so it appends the logs to the last lot
instead of overwriting them (WITH NOINIT). Next time the secondary
does manage to apply the logs. Now I thought that would mean it gets
ALL the logs and applies them, except I get this error message:
Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
begins at LSN 1247944000000500300001, which is too late to apply to the
database. An earlier log backup that includes LSN
1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Now I know that means that basically there is a gap between the last
log to be applied and the one we're attempting to apply now. What I
don't understand is how that can be since the last lot of logs to be
successfully applied included more than one BACKUP LOG's worth. How
can I tell RESTORE LOG to apply ALL the log backups appended together
instead of just the first ones in the set?
TIA
Niall> How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
You can't. So you have to write some code that uses RESTORE HEADERONLY, and based the result does
several RESTORE LOG commands.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<niallporter@.yahoo.co.uk> wrote in message
news:1158846017.059168.76240@.m73g2000cwd.googlegroups.com...
> Hi all,
> I have a home-grown log shipping setup at work which I'm trying to
> modify. At present if the standby server cannot apply the logs before
> the live server writes a new set (usually because the db cannot be
> locked), it fails and we have to do a full resync between the servers.
> I've put in a mechanism whereby the status of the last attempt to apply
> the logs to the standby database is recorded. If the primary sees that
> the last attempt was successful it does a BACKUP LOG ... WITH INIT.
> If it sees the last attempt was not successful it does the same but
> WITH NOINIT, which I gather appends the new logs onto the end of the
> current lot. This way the transaction logs will queue up every hour
> until I can solve whatever the issue between the servers is so they can
> be applied, thus saving a full 4 hour resync on a 220GB database.
> Problem is this. For example, the secondary can't apply logs because
> it can't get exclusive lock on the db. It marks that database as out
> of sync in a table. Next time the primary backs up logs it sees the
> secondary is not up to date so it appends the logs to the last lot
> instead of overwriting them (WITH NOINIT). Next time the secondary
> does manage to apply the logs. Now I thought that would mean it gets
> ALL the logs and applies them, except I get this error message:
> Executed as user: STEWARTMILNE\sqlosp1. The log in this backup set
> begins at LSN 1247944000000500300001, which is too late to apply to the
> database. An earlier log backup that includes LSN
> 1247592000000017600001 can be restored. [SQLSTATE 42000] (Error 4305)
> RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
> The step failed.
> Now I know that means that basically there is a gap between the last
> log to be applied and the one we're attempting to apply now. What I
> don't understand is how that can be since the last lot of logs to be
> successfully applied included more than one BACKUP LOG's worth. How
> can I tell RESTORE LOG to apply ALL the log backups appended together
> instead of just the first ones in the set?
> TIA
> Niall
>