Jamie Scharbrough (10/17/2012)
The answer to this is: It depends.I guess I'm approaching this with the view that this is a learning enviornment, and so you should do all the steps for practice.
That's certainly legit, but I'm trying to comprehend some justifications for various strategies at the same time.
Let's say you do the three different backups and find that your 4 pm trans doesn't work. If you find that your 3 and 2 pms also don't work...well, personally, if I was in a test or learning enviornment...I'd be glad I chose to have a more complex set up, because essentially, it caught the problem earlier. At this point, you'd stop and try and figure out what the screw up is.
Yes, you'll lose data, but would you have lost MORE data if you'd only done the two backups?
I think I'm not explaining myself correctly. Here's a more detailed try:
I can do a nightly full and an hourly trans (9am, 10am, 11am...)
- or -
a nightly full and an hourly differential (9am, 10am, 11am...)
In the first case, if I want to recover to 3pm status, I'll have to use the nightly full and the 9am trans, the 10am trans, the 11am trans, the 12noon trans, the 1pm trans, the 2pm trans and the 3pm trans, to recover all the things that happened that day.
In the second case, to get to 3pm, I need only the nightly full and the 3pm diff, no?
If I comprehended the concept of the trans and differential correctly, the differential carries everything since the last full, while the trans carries only the stuff since the last trans, potentially requiring numerous steps to recover to a certain point, while a diff can do it in one step, at the cost of more space used in the backup file.
Also, if one of the trans files is corrupt, everything after that is unusable, since trans log restores build on one another. If one of the diff files is corrupt, the one just after may well be okay. In a trans recovery chain, -every- file in the chain has to be okay. Restore from a diff file is not dependent on any other diff files.
No, I'd like an hourly snapshot, I'm just not clear on what is the best way of achieving it.
I'm not sure that's possible, as snapshots only include full backups. There may be a way to do this via a 3rd party tool; I'd have to look into this. (I'm pretty new to snapshots). You can use them for differentials, but you'll need the VSS interface.
I think my terminology was sloppy here. I meant a backup-restore point, of the sort achieved by one of the normal SQL backup operations, not something exotic.