July 6, 2011 at 3:53 pm
Hello,
After examining a database corruption and restore scenario i have questions about the backup chain.
If we started a diff backup on a database this will only backup the altered extents....from what i believe it will not contain any log information. It does, however, create a new checkpoint in the log and restarts the backup chain.
My questions are:
- during the diff backup, if a log backup is taken, will it be part of the previous or new chain??
- do you have to wait for the diff backup to complete before the next log backup becomes the first in the new chain??
- what happens to transactions committed during the diff backup that began before the backup started, when there is a log backup before the diff finishes??
depending on the answers there may be a question about recovery :p
July 6, 2011 at 3:59 pm
lilywhites (7/6/2011)
Hello,After examining a database corruption and restore scenario i have questions about the backup chain.
If we started a diff backup on a database this will only backup the altered extents....from what i believe it will not contain any log information. It does, however, create a new checkpoint in the log and restarts the backup chain.
a diff backup does not restart a log chain (neither does a full backup) so the questions are redundant! 🙂
---------------------------------------------------------------------
July 6, 2011 at 4:11 pm
ok i may have worded my question incorrectly....try this:
when creating a recovery solution you can avoid restoring massive numbers of log files by running differential backups daily instead of fulls
so....if you take a full on sunday at 4am, a diff on wednesday at 4am and tlogs every 5mins past the hour....if the db goes down at 9:20 and has to be restored....forgetting tail log backup....to restore to wednesday 9:05am you need sunday full, wednesday diff and 5:05am-9:05am log backups
say your diff takes 3+ hours and completes at 7:45....do you need tlogs from 4:05 onwards or 8:05 onwards??
July 6, 2011 at 5:56 pm
from msdn
A data or differential backup contains sufficient transaction log records to allow rolling forward the active transactions as part of restoring each backup. Each backup also contain sufficient log to roll back uncommitted transactions to bring the database into a state that is transactionally consistent and usable.
So log backups after the completion of the diff backup.
I recommend you do a test so you are comfortable with the restore sequence.
---------------------------------------------------------------------
July 7, 2011 at 4:02 am
ok thanks George....we will be running full recovery tests but that question got me stumped and wasnt able to answer it.
It relates to one of my original questions....which is:
if there is a transaction that started before the differential which finished during the differential, and then there was a log backup, then the differential finished, then another log backup taken....if we can only use the log backups from after the differential finished....what are our options on rolling forward/backward the committed transactions that may have been caught up in the middle??
when the database was restored i was not able to be involved but have been asked to confirm that out backup strategy is sound. due to limitations in our backup environment, backups that take multiple hours, even with compression, are common. i need to figure out what happens during the backup process with regards to the logs as there are normally out-of-hours processes running during the times when differentials are scheduled. i want to know if we are able to recover any transactions that may not be picked up once the diff has completed or if we need to alter pur strategy?
thanks for the help
July 7, 2011 at 4:08 am
there's no point in time recovery with differentials , its just to the end of the differential, so thats your recovery point.
If you want to recover to a point in time during the differential backup run, you would have to leave out the differential and just roll through the transaction log backups that cover the differential backup window.
---------------------------------------------------------------------
July 7, 2011 at 4:10 am
thats what i was looking for!!! surprising i havent been able to find that states anywhere until now!!
i will look into making changes to stop tlog backups during the differentials until we are able to introduce a new backup system.
thanks for your help 🙂
July 7, 2011 at 4:17 am
lilywhites (7/7/2011)
thats what i was looking for!!! surprising i havent been able to find that states anywhere until now!!i will look into making changes to stop tlog backups during the differentials until we are able to introduce a new backup system.
thanks for your help 🙂
sorry, why do you want to do that? I thought you wanted to be able to cover restoring to a point in time during the differential backup window.
You can do that whether or not there is a log backup during the differential, the log backup after the diff will just be larger as it covers a longer period. As long as you have an unbroken log chain you will be able to recover to any point in time.
---------------------------------------------------------------------
July 7, 2011 at 4:22 am
lilywhites (7/7/2011)
i will look into making changes to stop tlog backups during the differentials until we are able to introduce a new backup system.
Why? They're able to work together and you're opening yourself to larger potential data loss if you do that.
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
July 7, 2011 at 4:25 am
i was thinking it may be easier to stop the log backups for a couple hours as opposed to having to increase the number of logs for restores (and restore tests) and would avoid confusion if our hosting department has to start the restore (funny sharing of responsibilities im trying to alter!!). if it was just me that was responsible for restoring it wouldnt be a problem....
although pausing the tlog backup during out-of-hours processing may incurr too much overhead as woudld be multiple GB of tlog backups after just 3 hours....
i will give this some thought and speak to my manager and the hosting team to see what our options are....ideally we should replace the backup system to allow quicker backups....but we will see....its cheaper to make a process change unfortunately 🙁
thanks again to all
July 7, 2011 at 4:45 am
what backup system is it currently?
Pls confirm the SQL version is in fact SQL2005.
---------------------------------------------------------------------
July 7, 2011 at 5:25 am
hi george,
currently we backup multiple SQL 2005 and SQL2008 servers to a SATA disk location....but this is old and very slow and we are unable to write multiple backups at the same time....so the limitations and speed problems.
the backup in question is for SQL2005 but using LiteSpeed engine for compression due to the size
July 7, 2011 at 5:36 am
backing up across a network will always be slow. Obvious speed up is to backup to local disk and then copy off to the SATA drive later.
---------------------------------------------------------------------
July 7, 2011 at 5:42 am
yeah i know....but we dont have the space on most systems....or the budget to allocate it if the customer decides they will not pay for it....
im still finding it hard to only give the customer what they pay for rather than what they should have to ensure a robust backup solution....but then again the point of hosting is to make profit....so we have to give the best we can with the equipement we have
should be reclaiming some hardware shortly so hoping i can persuade them we need to upgrade the backup architecture....easier said than done as they dont get any noticable advantage unless something goes wrong....which isnt very often
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply