Restoring the source db with log shipping.

Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas

"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.
|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.
|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas

"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.

Restoring the source db with log shipping.

Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.sql

Restoring the source db with log shipping.

Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.If you use the Log Shipping mechanism, it begins with a snapshot backup of
the primary database and restores it in standby mode. Then, as you ship
transaction log backups to the standby server, the transaction log backups
are automatically restored, again in standby mode, to the secondary server.
You could do this manually with FTP and scheduled tasks if you prefer, but
the log shipping features do automate this for you.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:OCH70aPKFHA.1604@.TK2MSFTNGP10.phx.gbl...
Although BOL has good information on log shipping, I'm struggling to
understand what happens (or what you should do) when you restore the Source
database.
Does log shipping somehow automatically ensure that the Destination server
is updated when the Source server is restored? Or is a manual RESTORE
required on each Destination server as well?
Cheers,
Paul Ritchie.|||Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.|||Once you begin the sequence, all you ever have to do is apply transaction
log backups; however, if you do major work on the primary database, it would
be a good idea to create another Full backup of the primary and restore it
to the secondary, in standby mode. I prefer to do this manually, but I
think the log shipping mechanism has a mechanism for this as well.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:ey$zJkQKFHA.508@.TK2MSFTNGP12.phx.gbl...
Thanks Anthony - much appreciated.
As you say "it begins with a snapshot backup...". However what I was
trying to ask was: what happens when I restore the primary database? eg I
go into EM and right click and restore a primary database.
Does this snapshot backup then get automatically created, shipped and
restored on the standby server, or would I have to perform this or some
other task manually as well?
cheers,
Paul.

Restoring SQL2000 Transaction Logs to SQL2005

Hey all,

Just went through migrating to SQL 2005 as well and we have a need to apply log shipping between SQL 2000 and SQL 2005. Well not shipping per say in the automated fashion that SQL 2005 offers but rather we need to apply SQL 2000 transaction logs to a SQL 2005 instance. Though the database is still in SQL 2000 version 80 and the secondary database needs to be available in read only. (STANDBY MODE)

When I attempt to restore either a FULL backup or transaction log I get the error:

RESTORE DATABASE is terminating abnormally. This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY

The only way I could restore the FULL was by using the WITH RECOVERY OR NORECOVERY option during the restore process. The issue with this is I need the database in standby by mode in order to continue to append transaction logs.

Anybody have any thoughts how I might be able to get around this?

Thanks

Eric

The physical structure of the database has changed between SQL2000 and SQL 2005. Since the transaction logs contain low-level data, you cannot apply SQL 2000 transaction logs to a SQL 2005 database, or vice-versa

|||

Bummer, ya figure MS would build the 2005 transactional restore with a backward compatibility method, (in the since of restoring a database in STANDYBY mode) though what’s the point of keeping a database in (80) SQL 2000 mode if it truly is a semi-upgraded 2005 database.
Please correct me if I am wrong here. but I understand the reason why the 2000 versioned database is it can't be restored in stand by mode without created a log of is own during the upgrade.( not to repeat myself but if the destination database is in SQL 2000 format yet is attached to a SQL 2005 instance) it shouldn't have a issue, that is if SQL 2005 was truly backward compatible.

Thanks

|||

OK, There are several things here, and I'll try to address them all.

First off, there is a common misconception about compatibility modes and what they are.

Setting a compatibility mode governs how TSQL is interpereted and behaves, but does NOT have any impact to the physical structure of the database itself. So, a SQL 2005 database in 80 compatibility mode will have a SQL 2005 structure in the database files, but will respond to TSQL as if it were a SQL 2000 database. Meaning that any deprecated/removed keywords will be honored, and any behaviors which have been changed will act as they did in SQL 2000.

When you upgrade to SQL 2005, the database structures are upgraded at the point in time when the database is recovered.

So, you can continue to apply SQL 2000-based logs to the database as long as it is never recovered. Unfortunately, you cannot access it for any purpose other than applying logs. You cannot put it in standby mode, as that implies recovering the database, which triggers the update. You also cannot create a database snapshot without recovering/upgrading the database.

|||

Hmm, well that make since. i was a little shady on the compatibility modes though if the database structure does not change, they whats the big deal with attaching a once sql 2000 database to a 2005 instance and restoring a sql 2000 transaction file to it. ( during the attach process does the database automatically get upgraded?)

Thanks

|||

Part of attaching a database involves recovering it. This triggers the upgrading of the structures.

There is currently not a CREATE DATABASE FOR ATTACH WITH NORECOVERY.

Restoring Server which utilizes Log Shipping

I have a large database that gets a full backup every night. We
utilize Log Shipping from this database to another database on a
reporting server. During the day Log Shipping creates log files on
the local server and truncates the transaction log for the database.
Everything works great. Lets say at 2:00pm the database crashes. I
restore the database from the previous nights full backup. Now I want
to restore the transaction log up to 2:00pm. The problem is that it's
empty because the log shipping has truncated it throughout the day.
What I do have is over 1000 small transaction files that were created
for log shipping. How can I apply all 1000 transaction files in the
shortest amount of time? I won't even think about doing each one
seperately.
Will
The Log shipping wizard will only truncate the log after it has backed it up
so they are still fine for a restore. Why do you have 1000 log files?
You only need the ones from the last full backup. How often are you issuing
log backups? You might consider doing differentials during the day as well
if you backup the log frequently to save restore time. Then you only have
to restore logs from the last Diff.
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407300924.10482dca@.posting.google.c om...
> I have a large database that gets a full backup every night. We
> utilize Log Shipping from this database to another database on a
> reporting server. During the day Log Shipping creates log files on
> the local server and truncates the transaction log for the database.
> Everything works great. Lets say at 2:00pm the database crashes. I
> restore the database from the previous nights full backup. Now I want
> to restore the transaction log up to 2:00pm. The problem is that it's
> empty because the log shipping has truncated it throughout the day.
> What I do have is over 1000 small transaction files that were created
> for log shipping. How can I apply all 1000 transaction files in the
> shortest amount of time? I won't even think about doing each one
> seperately.
> Will
|||We have over 1000 log files because we log ship every 2 minutes. We
need to do this for data accuracy on our reporting server. If we do our
full backup at 2:00am and then during the day say at 4:00pm our server
crashes. We can restore the database from the full nightly backup. But
then we need to roll the database forward to 4:00pm using the
transaction log. Since log shipping trunctates the logs after every log
shippment, this leaves us with a small transaction file that contains
the transaction for a 2 minute period.
As you can see we can build up alot of transaction log files quickly.
What we need to be able to do is run all fo the transaction files
sequentially until they are complete. Is there a tool to do this or any
process that someone has used?
Will
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring Server which utilizes Log Shipping

I have a large database that gets a full backup every night. We
utilize Log Shipping from this database to another database on a
reporting server. During the day Log Shipping creates log files on
the local server and truncates the transaction log for the database.
Everything works great. Lets say at 2:00pm the database crashes. I
restore the database from the previous nights full backup. Now I want
to restore the transaction log up to 2:00pm. The problem is that it's
empty because the log shipping has truncated it throughout the day.
What I do have is over 1000 small transaction files that were created
for log shipping. How can I apply all 1000 transaction files in the
shortest amount of time? I won't even think about doing each one
seperately.
WillThe Log shipping wizard will only truncate the log after it has backed it up
so they are still fine for a restore. Why do you have 1000 log files?
You only need the ones from the last full backup. How often are you issuing
log backups? You might consider doing differentials during the day as well
if you backup the log frequently to save restore time. Then you only have
to restore logs from the last Diff.
--
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407300924.10482dca@.posting.google.com...
> I have a large database that gets a full backup every night. We
> utilize Log Shipping from this database to another database on a
> reporting server. During the day Log Shipping creates log files on
> the local server and truncates the transaction log for the database.
> Everything works great. Lets say at 2:00pm the database crashes. I
> restore the database from the previous nights full backup. Now I want
> to restore the transaction log up to 2:00pm. The problem is that it's
> empty because the log shipping has truncated it throughout the day.
> What I do have is over 1000 small transaction files that were created
> for log shipping. How can I apply all 1000 transaction files in the
> shortest amount of time? I won't even think about doing each one
> seperately.
> Will

Restoring Server which utilizes Log Shipping

I have a large database that gets a full backup every night. We
utilize Log Shipping from this database to another database on a
reporting server. During the day Log Shipping creates log files on
the local server and truncates the transaction log for the database.
Everything works great. Lets say at 2:00pm the database crashes. I
restore the database from the previous nights full backup. Now I want
to restore the transaction log up to 2:00pm. The problem is that it's
empty because the log shipping has truncated it throughout the day.
What I do have is over 1000 small transaction files that were created
for log shipping. How can I apply all 1000 transaction files in the
shortest amount of time? I won't even think about doing each one
seperately.
WillThe Log shipping wizard will only truncate the log after it has backed it up
so they are still fine for a restore. Why do you have 1000 log files?
You only need the ones from the last full backup. How often are you issuing
log backups? You might consider doing differentials during the day as well
if you backup the log frequently to save restore time. Then you only have
to restore logs from the last Diff.
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407300924.10482dca@.posting.google.com...
> I have a large database that gets a full backup every night. We
> utilize Log Shipping from this database to another database on a
> reporting server. During the day Log Shipping creates log files on
> the local server and truncates the transaction log for the database.
> Everything works great. Lets say at 2:00pm the database crashes. I
> restore the database from the previous nights full backup. Now I want
> to restore the transaction log up to 2:00pm. The problem is that it's
> empty because the log shipping has truncated it throughout the day.
> What I do have is over 1000 small transaction files that were created
> for log shipping. How can I apply all 1000 transaction files in the
> shortest amount of time? I won't even think about doing each one
> seperately.
> Will|||We have over 1000 log files because we log ship every 2 minutes. We
need to do this for data accuracy on our reporting server. If we do our
full backup at 2:00am and then during the day say at 4:00pm our server
crashes. We can restore the database from the full nightly backup. But
then we need to roll the database forward to 4:00pm using the
transaction log. Since log shipping trunctates the logs after every log
shippment, this leaves us with a small transaction file that contains
the transaction for a 2 minute period.
As you can see we can build up alot of transaction log files quickly.
What we need to be able to do is run all fo the transaction files
sequentially until they are complete. Is there a tool to do this or any
process that someone has used?
Will
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Well you can use the TSQL commands such as RESTORE HEADERONLY to get
information about the logs. From there you can determine the proper order
to restore them but it will take a little bit of code to loop thru each of
the files and get the information etc. There are some 3rd party products
who do this type of thing as well. SQL LiteSpeed is one that I know of
that has a gui that will look in a folder and figure out the correct
sequence to restore the files with.
Andrew J. Kelly SQL MVP
"Will Wirtz" <willcwirtz@.yahoo.com> wrote in message
news:%23eVuCiReEHA.720@.TK2MSFTNGP11.phx.gbl...
> We have over 1000 log files because we log ship every 2 minutes. We
> need to do this for data accuracy on our reporting server. If we do our
> full backup at 2:00am and then during the day say at 4:00pm our server
> crashes. We can restore the database from the full nightly backup. But
> then we need to roll the database forward to 4:00pm using the
> transaction log. Since log shipping trunctates the logs after every log
> shippment, this leaves us with a small transaction file that contains
> the transaction for a 2 minute period.
> As you can see we can build up alot of transaction log files quickly.
> What we need to be able to do is run all fo the transaction files
> sequentially until they are complete. Is there a tool to do this or any
> process that someone has used?
> Will
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql

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
>