September 20, 2010 at 12:18 pm
Hello Everyone
I am performing log shipping between two boxes. But I have had to write the code for the secondary box because the secondary box is in a DMZ. Everything is working fine, except for one thing.
Every time the Full backup fires off on the primary, my Log Shipping LSN numbers get out of sequence. So my restore on the secondary fails. At that point, I must manually get the two back in sequence.
Is there a way to continue to take a Full backup on the primary without causing issues with the trans log backups on the Primary? Or getting the LSN numbers out of sequence.
Thanks
Andrew SQLDBA
September 20, 2010 at 12:51 pm
does this relate to your other log shipping post andrew?
Full backups do not affect the log chain. the full has not had a step added to truncate the log has it?
---------------------------------------------------------------------
September 20, 2010 at 12:58 pm
HI George
Yes, in a way. My LSN numbers are getting out of sync when the restore process tries to restore a log file that was just deleted.
I cannot figure out why the log file is being deleted, or is not in the directory. It seems this is not happening on any type of a schedule, it is rather random.
So my restore process will be missing a log file that needs to be restored, and does not exist. At that time, I have to start over by taking a full backup of the primary side, and restoring it on the secondary side. and then starting my two sql jobs that does the actual restore process.
I would like to find out if I can have the primary not delete the old files. Or something
Thanks
Andrew SQLDBA
September 20, 2010 at 1:05 pm
Full backups do not truncate the transaction log. They do not break log shipping.
Look at what else is happening at the time of the full backup. A switch to simple recovery perhaps (for index rebuilds), a separate log backup apart from the log shipping maybe?
What I've seen before (which caused much this kind of issue) was a server that had two backup jobs.
1) Log backup every 30 min, into directory A, shipped to server Z
2) Full backup followed by log backup daily, into directory B, not shipped to server Z
So it looked like the full backup was truncating the transaction log, but in reality is was because the person that set up the full database maintenance plan put both full and log backup tasks in there.
Check MSDB (backupset?) tables for the backup history. Should allow you to identify where the missing log backup is (if there is one)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 1:06 pm
so your two posts refer to the same problem? Could you refer people over to here, it has more info.
---------------------------------------------------------------------
September 20, 2010 at 1:22 pm
presuming your problem is not one of the obvious ones above, can you give us a quick description of you in-house written log shipping set up?
sounds like this is not the MS supplied log shipping set up?
Are you using a maintenance plan for the log backup?
Do you have a bespoke copy process?
Have you tried using the wizard, running the two SQl servers under local accounts of the same name with the same password and giving access to a share?
---------------------------------------------------------------------
September 20, 2010 at 2:40 pm
AndrewSQLDBA (9/20/2010)
Hello EveryoneI am performing log shipping between two boxes. But I have had to write the code for the secondary box because the secondary box is in a DMZ. Everything is working fine, except for one thing.
Every time the Full backup fires off on the primary, my Log Shipping LSN numbers get out of sequence. So my restore on the secondary fails. At that point, I must manually get the two back in sequence.
Is there a way to continue to take a Full backup on the primary without causing issues with the trans log backups on the Primary? Or getting the LSN numbers out of sequence.
Thanks
Andrew SQLDBA
Hello,
The database involved in Logshipping should not be included in the manitenance plan as if you take a backup of it, it will break the log sequence.
Instead use COPY_ONLY backup if you want to take backup of the primary database.
As Log shipping is DR solution, why do you want to take full backup of the primary ?
Are you using native SQL backups or any third party software like red gate to take backups ?
HTH,
CHeers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
September 20, 2010 at 2:44 pm
GilaMonster (9/20/2010)
Full backups do not truncate the transaction log. They do not break log shipping.
Hello Gail,
I cannot understand this "They do not break log shipping" as I am under the impression that the database involved in Log shipping should not be backed-up after the log shipping is set up and running as taking a full backup will break the LSN.
Can you explain How a full backup of the db involved in LS will not break LS ?
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
September 20, 2010 at 2:52 pm
That is exactly what I am experiencing. I have stopped the full backup of the Primary. We back that Db up to keep a copy.
I was just reading about Copy_Only clause.
I was experiencing the chain being broken when a full backup was taken. I am testing this now, I will know in a little while if it breaks it again. I am hoping not, I am really tired of working with log shipping. LOL
Thanks
Andrew SQLDBA
September 20, 2010 at 2:52 pm
SQL_Quest-825434 (9/20/2010)
The database involved in Logshipping should not be included in the manitenance plan as if you take a backup of it, it will break the log sequence.Instead use COPY_ONLY backup if you want to take backup of the primary database.
Full database backups DO NOT truncate the transaction log. They do not (and never have) broken the log sequence.
The only things that can break a log chain are a missing LOG backup (not full or differential), a switch to simple recovery or an explicit log truncation (backup log ... with truncate_only)
What COPY_ONLY does (for full backups) is not reset the differential base, so that further differential backups are not affected by a full database backup.
You can take full backups without affecting log shipping, without affecting log restore sequences. This has been true from at least SQL 7.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 3:02 pm
It is trivial to prove that full backups do not break the log chain. Thusly: (you'll have to change the backup destination to fit what you have)
CREATE DATABASE TestingLogRestore
GO
ALTER DATABASE TestingLogRestore SET RECOVERY FULL
GO
USE TestingLogRestore
GO
CREATE TABLE t1 (
id INT
)
INSERT INTO t1 VALUES (1)
go
BACKUP DATABASE TestingLogRestore TO DISK = 'D:\Develop\Databases\Backups\LogRestore1.bak' -- full backup 1
GO
INSERT INTO t1 VALUES (2)
GO
BACKUP LOG TestingLogRestore TO DISK = 'D:\Develop\Databases\Backups\LogRestore1.trn' -- first log backup
go
INSERT INTO t1 VALUES (3)
GO
BACKUP DATABASE TestingLogRestore TO DISK = 'D:\Develop\Databases\Backups\LogRestore2.bak' -- full backup 2
GO
INSERT INTO t1 VALUES (4)
GO
BACKUP LOG TestingLogRestore TO DISK = 'D:\Develop\Databases\Backups\LogRestore2.trn' -- second log backup
go
USE master
GO
DROP DATABASE TestingLogRestore
-- restore the first full
RESTORE DATABASE TestingLogRestore FROM DISK = 'D:\Develop\Databases\Backups\LogRestore1.bak' WITH NORECOVERY
-- restore the first log backup
RESTORE LOG TestingLogRestore FROM DISK = 'D:\Develop\Databases\Backups\LogRestore1.trn' WITH NORECOVERY
-- restore second log backup. If full backups truncated the transaction log, this would fail. However...
RESTORE LOG TestingLogRestore FROM DISK = 'D:\Develop\Databases\Backups\LogRestore2.trn' WITH NORECOVERY
-- and bring it online
RESTORE DATABASE TestingLogRestore WITH recovery
SELECT * FROM TestingLogRestore.dbo.t1 -- values 1..4
go
DROP DATABASE TestingLogRestore
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 3:03 pm
AndrewSQLDBA (9/20/2010)
I was experiencing the chain being broken when a full backup was taken.
Andrew, there's something else happening that's breaking the log chain, because full backups do not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 3:11 pm
Or, if you don't want to believe me, how about someone who used to work on the storage engine team for SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 3:16 pm
GilaMonster (9/20/2010)
Or, if you don't want to believe me, how about someone who used to work on the storage engine team for SQL 2005.
I believe you 🙂
andrew, will you describe your process? especially what other steps are run when you run a full backup
---------------------------------------------------------------------
September 20, 2010 at 3:18 pm
GilaMonster (9/20/2010)
AndrewSQLDBA (9/20/2010)
I was experiencing the chain being broken when a full backup was taken.Andrew, there's something else happening that's breaking the log chain, because full backups do not.
Perfect Gail .. That cleared my misconception. The link to Paul's site explains and clears the misconception 🙂
Thanks much !
Cheers
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply