July 31, 2015 at 11:51 am
Newbie question, but I've been unable to locate the answer so far, so I thought I'd ask here...
I've got a decent handle on restore/recovery, but what I'm still not clear on is the relationship between the timings at which a full database backup occurs, and a t-log backup occurs, and how that relates to recovery when the timings overlap. For example:
8:30-8:35pm - T-log backup
9:30-9:35pm - T-log backup
10:00-10:45pm - full backup
10:30-10:35pm - T-log backup
11:30-11:35pm - T-log backup
In this case, the hourly 10:30 t-log backups coincide with the full backup which started at 10pm, since it takes the full backup 45 minutes to complete. So is the 10:30pm t-log backup applicable, since the start of the full backup was prior to the start of the t-log backup?
Or do you always look at the completion time of the full backup, and apply only those t-logs which start after the backup completes?
Thanks,
--=Chuck
July 31, 2015 at 12:10 pm
The short answer is that the 10:30-10:35 log would not be used with the 10:00-10:45 full. The easy way to think of it is that the restoring the full backup will bring the database to a state as it was at a single point-in-time, and as Paul Randal says in https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx, that point is basically the end of the backup.
The section of that article on exactly what a full backup does will help clear up why that would be.
Cheers!
August 1, 2015 at 2:08 am
The first log you need is the first one which started after the data-copying portion of the full backup completes. That's usually very close to the end of the backup. The only time you usually need to worry is when the full and a log backup complete around about the same time
That said, there's no harm in being safe and starting with one log earlier. If it's not required SQL will give you an error message saying that the log file is too old. It won't mess up the restore sequence or cause other problems.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply