October 5, 2004 at 4:02 pm
hi,
i'm trying to figure out a reasonable backup strategy and am puzzled by the following:
if i can restore to the last differential backup (assuming I've done the last full backup restore first), what happens if I do not have my transaction log backups?
Is my system restored to the last point when the differential backup completed or do I need my transaction log backups to recover from the full backup point to the differential backup point? i can't find this explained clearly anywhere and probably don't understand transaction log backups too well.
basically i'm trying to figure out a backup strategy where i have sufficient disk space (in raid 5) but limited tape space. what i'm considering is doing a full backup once a week, differential backups daily and transaction log backups hourly, all to disk. I would then like to write just the full backup once a week and differential backups daily to tape without backing up my transaction log backups to tape.
what i'm thinking is that if i need to restore and the disk is fine, then i can restore to the last transaction log backup but if i need to restore and the disk is pooched, i can restore to the last differential backup from tape. does this make sense? or do i need to backup the transaction backup logs as well to tape in order to perform a differential backup, which i don't really have the space for.
any help is much appreciated,
cheers,
ant.
October 5, 2004 at 8:31 pm
If you restore a differential backup, you just need to restore the trx log backups made after the differential backup. You do not need to apply all the logs made since the last full backup (assuming you do not truncate the log, and restore with the NORECOVERY and STANDBY options).
Your understanding is correct. The only potential issue is that you stand to lose a full day's worth of data, assuming a worse case scenario where the disk is 'pooched' just before you perform the differential backup to tape for the day.
If space is a constraint, you might want to use a 3rd party tool such as ours to compress the backups.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 6, 2004 at 10:46 am
ok thanks peter, but what if i do not have ANY transaction log backups?
then after restoring the differential backup, are all data/committed transactions current to the point of the differential backup?
October 6, 2004 at 3:35 pm
yes, you will be current to the point of the diff.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply