I have a database and can afford to loose max one hour data.I am trying to figure out if there is a difference in following two setups from backup and recovery stand point.
1. DB with simple recovery model, setup full backup at 12 am and differential backup every 1 hour between 6am to 11pm. this will keep transaction log not grow too big.
2. DB with full recovery model, setup full backup at 12 am and transactional backup every 1 hour between 6am to 11pm
#2 is the better option. Frequent log backups will reduce the log file growth. You're better off backing up the log at 15 minute intervals, or even more frequently. This reduces your data loss window.
Also gives the possibility to recover the tail after a crash - more importantly allows a recovery across a missing full backup (if you have the previous logs and full.
The diffs also often have problems that they get bigger up to the next full - if a lot of data is updated after the full backup they can be similar to doing a full backup every hour.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Thanks for the reply. Just from backup and recovery stand point, both setup will do the same, right?
No as diffs don't give you point-in-time recovery, only tlog backups give that. Plus you're going to have a lot more storage needs with hourly diffs. Do diffs maybe 12 hours after the full backup or daily diffs and weekly fulls.
Here's our setup: 1. Nightly full backup at 2am 2. Diff at 2pm 3. Tlog backups every 15 minutes
Please proceed with second method because Tlog backup is used to point in restore for Full Recovery model to database .This Tlog back will take the more storage but keep the Tlog within the minimum interval as per your requirement.
differential and transaction backup
Posted - 06/13/2012 : 10:19:10
