LogShipping

[Anu]

Hi,

I have a question.

We have created a Logshipping (customized).

(We didn't use the logshipping wizard SQL 2000)

On Primary server
The backup strategy is :

Everyday we take a Fullbackup followed by every 15 mins we take transaction log backup.

Day 1:

Full Backup at 12:00 AM
Transaction log backup from 4:00 AM to 11:45 PM

Day 2:

Full Backup at 12:00 AM
Transaction log backup from 4:00 AM to 11:45 PM

Day 3:
.
.
.

On Secondary server

Everyday will restore the full backup daily and followed by the transaction log backup restore with no recovery mode.


Day 1:

Full Backup Restore at 2:00 AM
Transaction log backup Restore from 5:00 AM to 12:45 AM

Day 2:

Full Backup Restore at 2:00 AM
Transaction log backup Restore from 5:00 AM to 12:45 AM

Day 3:
.
.
.


The problem we have is

A third server which is remote need the full backup for the first time only and everyday they need a log backup alone and not the daily fullbackup to logship the server. Will that be possible
using the logs alone for logshipping but not considering the full backup file.

Third server

Like:

One time Full Backup Restore at 4:00 AM


Day 1:

Only
Transaction log backup Restore from 9:00 AM to 4:45 AM

Day 2:

Only
Transaction log backup Restore from 9:00 AM to 4:45 AM

Day 3:
.
.
.


Don't the server throws an error saying that

Msg 4305, Sev 16: The log in this backup set begins at LSN 13000001816200001, which is too late to apply to the database. An earlier log backup that includes LSN 13000001723100001 can be restored. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE LOG is terminating abnormally. [SQLSTATE 42000]

We don't know how to fullfil the request. I don't think Logshipping wizard skips the full backup file for logshipping if it has been schedule for full backup everyday and logbackup every 15 mins.

Please help. is there anyway we can solve this problem.


Thanks,
Anu

[MAK]

How about differential backup?

[skhanal]

A full backup does not invalidate transaction log backups, that is the you can continue restoring transaction log backups in your second secondary server without restoring the daily full backup.

You will get the error message only if transaction log is truncated or you lost a file. If you look at the starting LSN of full backup and transaction log backup following the full backup, they are same.

[MAK]

She can get the error even if there is a checkpoint.

--drop database test11
create database test11
go
use test11
go
create table test(id int, name char(10))
go
backup database test11 to disk = 'd:\test11.bak' with init
go
insert into test select 1,'mak'
go

backup log test11 to disk = 'd:\test11_1.trn' with init
go
insert into test select 1,'skhanal'
go
insert into test select 1,'Anu'
go
insert into test select 1,'Claire'
go
backup log test11 to disk = 'd:\test11_2.trn' with init
go
insert into test select 1,'Anu1'
go
insert into test select 1,'Claire2'
go
checkpoint
go
backup database test11 to disk = 'd:\test11_2.bak' with init
go
insert into test select 1,'Anu2'
go
insert into test select 1,'Claire233'
go
backup log test11 to disk = 'd:\test11_3.trn' with init
go
use master
go

restore database test11 from disk = 'd:\test11.bak' with norecovery
restore log test11 from disk = 'd:\test11_1.trn' with norecovery
restore log test11 from disk = 'd:\test11_2.trn' with norecovery
restore log test11 from disk = 'd:\test11_3.trn' with norecovery

Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 7000000005100001, which is too late to apply to the database. An earlier log backup that includes LSN 7000000004000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

But without the checkpoint it runs fine.

[Anu]

I get the LSN error even if I try to continue.

I have replication going on my productions server.

I am rebuilding indexes which taken bigger transaction logs. So to avoid bigger transaction logs I am doing a transaction log backup for every index it creates, and after that I am taking a full backup.

So I cannot continue to logship without fullbackup this type of environment.

Please advise.

[skhanal]

Do you use TRUNCATE option?. Or change the database recovery model?

Checkpoint is a news to me. But running CHECKPOINT is not a normal situation. So for regular operation it will work.

[Anu]

no not at all

[MAK]

she cannot take tranlog backup if she uses truncate option (Simple mode).

[skhanal]

Only other operation that invalidates a TL backup are

A nonlogged operation such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement

An ALTER DATABASE statement that adds or deletes a file in the database

Do you have anything like that

[MAK]

One solution for this problem I can think of is

Create one more subscriber and take full backup tomorrow and for the rest of your life take tranlog backup and restore that on the server3.

[Anu]

Hi,

no nonlogged operation on production.

To Make sure,
I do have a replication server that has been replicated that stays in different Site replicated from the same production server in real time. We don't do any nonlogged operations on that.

All databases are in full mode. From that server I am logshipping to the DR Site Server. Even using that Logshipping
files I cannot able to logship only the logs.


Thanks,
Anu

[MAK]

Let me rephrase it.

One solution for this problem I can think of is

Create one more subscriber and take full backup of subscriber database (that you created now) tomorrow,
tranlog backup for the rest of your life and restore these on server3.

[Anu]

Scenerio 1:

Either I have to convince the users that I can either republish the data (transactional replication) directly to the third server (remote site).

Scenerio 2:

If they need only Logshipping. Then I have to republish the same database locally and take a first time backup and continue taking transactionlog backup for logshipping until the transactionlog breaks.

But It is double the cost.

Thanks for your suggestions guys.
Anu