November 2, 2008 at 1:50 am
Hi,
Iam planning to implement the following Backup strategy.There are 12 databases.
Full Backup daily 2am
Differential at every 6 hrs i.e 8am,2pm,8pm.
Log Backup at every 15 min
Here I have some questions:
1)After full backup of all the databases's is done then do we need to start the log backup?
Lets say 2am full backup is started n it takes 1 hr (3am)to complete backup of all 12 databases
Then after 3am we need to schedule log backup to run every 15 min? (starting from 3:15am)OR we need start the log backup at 2:15am itself?
2)At 8am diffrential will take place, so what happens to the log backup that supposed to run at 8am?(as per the schedule to run at every 15min,log backup also occurs at 8am)
3)Scheduling log backup every 15 min means should we lose 15 mins data when a disaster occurs?
Could you please explain me the above and how this backup strategy works?
thx in advance
November 2, 2008 at 1:58 am
Run the log backup job every 15 min from midnight. That's probably the safest way. Log backups aren't blocked by full backups in SQL 2005. even if they were, the log backup would simply wait for the backup to finish before running
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
November 2, 2008 at 7:40 am
Same for Diffs (as Gail mentioned). Just ignore the scheduling, they work around each other.
November 3, 2008 at 7:42 am
remember all your difs work from the baseline of the Last full backup Only and dont contain the difs from the last diff. so they get progressivly Larger during the Day.
November 3, 2008 at 10:43 am
Given that each subsequent diff backup contains all the changes since the last full backup, is it necessary to keep the previous diff backups? In a restore situation does one simply restore the last full backup then apply the most recent diff backup?
November 3, 2008 at 11:01 am
yep you just restore the latest diff backup
November 3, 2008 at 1:03 pm
los (11/3/2008)
Given that each subsequent diff backup contains all the changes since the last full backup, is it necessary to keep the previous diff backups?
It's a good idea. What happens if the latest diff is damaged and won't restore? Or if a user remembers that they deleted something really critical and they did the deletion just before the last diff. Sure, you can restore with the full and all the tran log backups, but that's time consuming.
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
November 7, 2008 at 8:31 am
Interesting:
Full BACKUP
LOG every 15 minutes
DIFF every 1 hour record a, b, c, d
LOG
LOG
LOG
LOG
DIFF 2 record a deleted.
So in this case
Restoref FULL BACKUP
Then the DIFF2 ?
November 8, 2008 at 3:15 am
Depends. Was record a deleted before Diff2 was taken, or afterwards?
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply