March 28, 2012 at 9:43 am
I have a question that a lot of senior DBAs have probably already dealt with. Can you do a differental backup with log shipping?
I have been trying for a few days now and it seems to be breaking "randomly" based on the differential backup. Lets just say we need to have differental backups because the DB is very (VERY) large and log shipping is one of our DR methods.
Let me know if I am barking up the wrong tree or if this isn't a possibility and if we should look at a different backup method.
Speaking of different; another option we had was to do nightly full backups and use the log shipping transactions as our differental backup.
Thanks
Adam
March 28, 2012 at 9:56 am
Adam Seniuk (3/28/2012)
I have a question that a lot of senior DBAs have probably already dealt with. Can you do a differental backup with log shipping?I have been trying for a few days now and it seems to be breaking "randomly" based on the differential backup. Lets just say we need to have differental backups because the DB is very (VERY) large and log shipping is one of our DR methods.
Let me know if I am barking up the wrong tree or if this isn't a possibility and if we should look at a different backup method.
Speaking of different; another option we had was to do nightly full backups and use the log shipping transactions as our differental backup.
Thanks
Adam
Slightly lost at what you are asking, can you expalin what it is you are trying to accomplish?
March 28, 2012 at 10:02 am
Which version of SQL Server are you using? If its Sql 2008 and later, u can compress the log backup.
Are u using any 3rd party tools.
To sync both the databases, u can have the nightly full backup and a fresh differential backup followed by an immediate tlog backup.
U can then sync both the databases by restoring these backup files on ur DR and initiate ur log shipping.
March 28, 2012 at 10:11 am
Adam Seniuk (3/28/2012)
I have a question that a lot of senior DBAs have probably already dealt with. Can you do a differental backup with log shipping?
Yes
Adam Seniuk (3/28/2012)
I have been trying for a few days now and it seems to be breaking "randomly" based on the differential backup. Lets just say we need to have differental backups because the DB is very (VERY) large and log shipping is one of our DR methods.
What do you mean by "break"?
Adam Seniuk (3/28/2012)
Speaking of different; another option we had was to do nightly full backups and use the log shipping transactions as our differental backup.
Logshipping is the "shipping of transaction logs" so what you say is pretty much what us "seniors" are expecting. No differentials are involved. Once the full backup is applied (once only), you apply the logs and choose to leave it non-accessible (no recovery) or in standby (readonly). You then simply keep restoring the additional logs to the logshipped database.
Differentials are normally just for recovery purposes.
March 28, 2012 at 11:14 am
Reply to: Lynn Pettis
Well to respond, I am trying to do incremental backups to save on backup space but also have log shipping to a secondary site just incase the primary site is lost due to failure I can get back up to using the log shipping database.
March 28, 2012 at 11:17 am
If what you are asking is will running a differential backup break the log chain for log shipping, the answer is no.
March 28, 2012 at 11:17 am
Reply to SS999
SQL 2008 R2 Enterprise
Well what we are noticing is that log shipping will fail after a differental backup and then we have to do a backup and restore to get the log shipping back up and running again.
I thought that log shipping and differental would play nicely and allow us the best of both worlds... and yes we have compression turned on but for the size of the DB compression won't really cut it.
March 28, 2012 at 11:21 am
Adam Seniuk (3/28/2012)
Reply to SS999SQL 2008 R2 Enterprise
Well what we are noticing is that log shipping will fail after a differental backup and then we have to do a backup and restore to get the log shipping back up and running again.
I thought that log shipping and differental would play nicely and allow us the best of both worlds... and yes we have compression turned on but for the size of the DB compression won't really cut it.
How does the log shipping fail after you complete a differential backup? Neither full or differential backups have any impact on the log chain.
March 28, 2012 at 11:22 am
Reply to MysteryJimbo
Break as in the LSN is now out of sync so it won't restore the log shipping on the secondary server.
Here is the error: Skipping log backup file because the log terminates at an LSN value what is too early to apply to the database.
Correct, so we have a differental running every X hours and we are running log shipping every X minutes.
Again this is to get recovery on site as well as for off site purposes.
March 28, 2012 at 11:26 am
There is a LSN mis-match. Find out the previously missed trn file and restore it.
March 28, 2012 at 11:30 am
Adam Seniuk (3/28/2012)
Reply to MysteryJimboBreak as in the LSN is now out of sync so it won't restore the log shipping on the secondary server.
Here is the error: Skipping log backup file because the log terminates at an LSN value what is too early to apply to the database.
Correct, so we have a differental running every X hours and we are running log shipping every X minutes.
Again this is to get recovery on site as well as for off site purposes.
Are you applying the differential file to same database as the log shipping?
March 28, 2012 at 11:31 am
So even after I did a full backup and restore with log backup and restore? Hrm... something is fishy.
I guess I am going to have to look at our backup software to see how it is actually setup because if its "differental" but it is really a log backup that would cause the log chain to break.
Thanks for the help guys. I will post here letting you know what I find out.
March 28, 2012 at 11:32 am
Reply to Lynn:
Nope just log shipping is being applied to the secondary server.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply