September 16, 2010 at 12:13 am
Dear All,
Can I schedule full backup of the logshipped/Primary/Source database during logshipping in SQLServer2000?
I think, it’s possible only in SQLServer2005 since it won’t break the log chain.
Can you confirm what will happen in SQLServer 2000 if we schedule full backup during logshipping?
Thanks in advance.
Regards,
Ravichandra.
September 16, 2010 at 3:41 am
I think, it’s possible only in SQLServer2005 since it won’t break the log chain.
that is the copy only option and yes its from 2005 onwards.
But you can take the backup of primary database any time.
So in case of recovery, you need to start the restoration process from this full backup and then further logbackup.
On secondary, you need to restore the full backup and then log chain will start as normal on secondary with whatever the frequency you have configured.
Without restoring your latest full or differential backup, your log restore on secondary will break.
And it doesnt matter which version of sql you have for secondary, you need to follow this on all version(till version 2008 i can confirm this)
----------
Ashish
September 16, 2010 at 3:46 am
ravisamigo (9/16/2010)
I think, it’s possible only in SQLServer2005 since it won’t break the log chain.Can you confirm what will happen in SQLServer 2000 if we schedule full backup during logshipping?
Full backups have never broken the log chain. Not in SQL 2005, not in SQL 2000. The copy_only (on a full backup) doesn't reset the differential base, that's all.
You can schedule full backups as you like. They won't break the log chain, they won't affect log shipping.
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 16, 2010 at 6:05 am
Hi Gail Shaw,
Thanks. I read the below information from one article.
"In SQL Server 2000 you won't be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log shipping.
In SQL Server 2005, yes its possible. You can take full backup of log shipped database and this won't affect the log shipping."
But copy-only backups were introduced SQL Server 2005, if we take full backup during logshipping in SQLServer 2000 it will break the log chain. rite?
Please advise.
Thanks and Regards,
Ravichandra.
September 16, 2010 at 6:09 am
like i suggested you earlier, your log restoration sequence will be broken on secondary till you not restore your latest full or differential backup and then onwards log backup
----------
Ashish
September 16, 2010 at 6:18 am
ravisamigo (9/16/2010)
Thanks. I read the below information from one article."In SQL Server 2000 you won't be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log shipping.
In SQL Server 2005, yes its possible. You can take full backup of log shipped database and this won't affect the log shipping."
The article is wrong. Full backups have never broken the log chain, not in SQL 2005, 2000 or 7 (don't know about before)
ashish.kuriyal (9/16/2010)
like i suggested you earlier, your log restoration sequence will be broken on secondary till you not restore your latest full or differential backup and then onwards log backup
It will not be broken. Neither full nor differential backups affect the log restore sequence. Only log backup and explicit log truncations truncate the log.
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 16, 2010 at 6:42 am
Thanks for correcting me gail.
yes, we can continue with log restoration on secondary even if we take the full backup on primary in between.
----------
Ashish
May 27, 2011 at 7:00 am
Is there any microsoft link for this.
Sorry Gila..but some persons only know Microsoft..
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply