That's what I would like to be able to do. But I'm still wondering, does this three-stage approach make that any more doable? If I take a differential backup every hour, I can (theoretically, I know) restore in two steps. The last full and the last differential. If I use transaction logs, it can be much more complex, no? As an example, say I have a DB that has a nightly full backup and an hourly differential during the course of a workday, starting at 9am. If the DB crashes at 4:30, I can restore from the nightly and the 4pm diff (or earlier, if it turns out the error occurred earlier and didn't show up until 4:30). If I use transaction log backups instead of differentials, I have to use the nightly full, then the 9am trans, then the 10am trans, then the 11am trans.... And if it turns out that the 4pm trans included a screwup, I have to start the whole process again, only stopping at 3pm, then if it's still there, again, stopping at 2pm... If I go with the full three-stage process, I have to use the last weekly full, then last night's diff, then all trans logs up to the problem point. Isn't that much more complex, with many more possible points of failure? Or maybe that's not how these things work and I don't yet understand all there is about the various backup methods.
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.
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?
And yes, there are more points of failure, but if you're doing this to practice automatic backups, you might as well catch the snags earlier, since you'll probably have to do all three backups with a larger database.
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 always do that - even if I don't agree with an opinion or don't use some advice, it often helps immensely to hear other people's thoughts on the matter, and having to explain my position clearly enough to get it across to someone else is a great aid in organizing my own thoughts.
But I can certainly use the practice, so I may set it up full-bore just to get familiar with the process. I'd just like to know clearly what I'm doing and why I'm doing it, rather than follow recipes blindly.
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2