Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
heliang
Starting Member
38 Posts |
 Posted - 2012-06-13 : 10:19:10
|
| 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 11pmThanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-13 : 10:32:06
|
| #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. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-13 : 11:23:41
|
| 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. |
|
|
|
heliang
Starting Member
38 Posts |
Posted - 2012-06-13 : 15:30:06
|
| Thanks for the reply. Just from backup and recovery stand point, both setup will do the same, right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-13 : 15:46:19
|
quote:
Originally posted by heliang
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 2am2. Diff at 2pm3. Tlog backups every 15 minutesTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
vivekinmadar
Starting Member
3 Posts |
Posted - 2012-07-02 : 09:57:55
|
| HI HeliangPlease 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.Regardsvivek |
|
|
|
|
|
|
differential and transaction backup