May 11, 2007 at 6:25 pm
Greetings,
I configured log shipping for a database on which backups, including transaction log backups were already configured. I saw the error 4305: ... The log in this backup set begins at LSN nnnn, which is too late to apply to the database. After some searching the web I found that the cause is due to full backups (if DB is in full recovery/Bulk-logged model, which mine is) resetting the LSN. WHen you setup a maintenance plan the first backup sets the LSN at some point and the subsequent backups are log backups so the sequence of LSNs is maintained.
Now my question. I thought that it was ok to take a full backup of the database which is log shipping as long as transaction logs are only backed up by the log shipping plan. But if the actual full backup is what actually updates the LSN then one should not take a regularly scheduled full backup of the database if that database is also log shipping. So, if I am log shipping should I or should I not take daily full backups of the database which is doing log shipping? What action actually updates the LSN? Previously I was doing daily full backups of a database which was log shipping and all seemed ok.
Can someone explain this to me in more detail please.
Thanks.
May 14, 2007 at 2:11 am
Bill, it is perfectly ok to take full backups whenever you want of your logshipped database, all the full backup does is put an entry in the log that a full backup has been taken. If LSN is out of sequence someone has either truncated the log or taken a log backup out side of your logshipping jobs.
---------------------------------------------------------------------
May 14, 2007 at 6:10 am
Hi Bill,
have a look at the job doing the full backup. If that job has a step that truncates the log (backup with truncate_only) you should convert this stap to a normal log backup...
regards
karl
Best regards
karl
May 14, 2007 at 9:57 am
Thanks for the responses. I guess the more basic question I am hoping to find the answer to is when does the LSN get updated? Is it the actual database backup or the backups of the transaction log? That is what I don't yet have a good understanding of.
Thanks.
May 14, 2007 at 11:52 pm
>> when does the LSN get updated
Technically, the databases' LSN get 'updated' when new transactions are created in the database. These transactions then get written to the transaction log file. When you then create a transaction log backup, the contents of the transaction log files are backed up, and the transaction logs are then 'flushed'. The FirstLSN and LastLSN values (viewable via RESTORE HEADERONLY) stored in the backup file tells us the transactions that are stored.
A full backup does not flush the transaction log files.
If you are having problems with the sequence, I would suggest checking the msdb..backupset/backupmediafamily tables to see if an unscheduled transaction log backup was made, which then threw off the restore sequence.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply