AmarettoSlim (10/17/2012)
After reading the posts on this thread I'm sensing you already kinda know the acceptable thresholds of data loss based on experience in the company.
I do. It's the paleontology department of a museum, I've worked here for years and have had numerous discussions with the scientists and curators about data - integrity, potential loss, redundancy, recovery, etc. I know how they work with the database - I wrote everything myself, and often help them with the work, or sit back and watch to see how well I judged their needs and expectations, based on how easily they can operate something I designed, so I'm very familiar with the entire operation.
You're dealing with very small databases, the IO cost to back them up is not daunting to your servers performance I'm sure (but then again I don't know the specs and you should check this before hand, I'm basing this off the low MB numbers you've mentioned and semi-modern hardware).
Yes, this machine very lightly loaded, and all activity is during normal working hours. At night I can have it do whatever I want.
I propose based on what I've read:
Full Backup - Weekly (day with least SQL activity)
Differential Backup - Every 12 hours (excluding the time frame you take your full backup)
Is there some reason why you don't recommend that I do an hourly snap? I would like to do that, and particularly, I would like to do it 'on demand', that is, when the database is in use. No point in making repeated copies of something that isn't changing, but I want to try something like a 'last date/time of ANY database mods' value, and use that in a script to determine whether or not to do an hourly backup (trans or diff, I'm still fuzzy on that).
You haven't mentioned retention much so you'll want to determine that, don't forget about it or one day your drive will fill with the backups 😉
I'll certainly keep an eye on that, but at the current rate of activity, the machine will probably wear out before I fill the drive.
From a recovery standpoint though it will be pretty simple, restore the latest weekly full backup and then restore the latest differential.
If you do the differentials at 12 AM and 12 PM (again, assuming that's acceptable regarding workload/performance) you could have a failure at 9 AM, and you restore back to midnight, or if its in the afternoon you restore back to 12 noon. While this doesn't give you point in time recovery based on your details it keeps the recovery well within acceptance. (DBs using this strategy should be in SIMPLE recovery model as Point-In-Time recovery is not needed, you will let the engine manage your .LDFs)
If I do it that way and use hourly differential backups, wouldn't I have almost effectively Point-In-Time, at least to the most recent hour? If the DB blows at 11:05, I can use the nightly full plus the 11am diff. If it blows at 10:55, I can use the nightly full plus the 10am diff. That gives me a very simple recovery model, with little potential data loss.
If your DBs start growing quickly, the IO costs are going to increase and you will need to re-adjust, keep that in mind. DR is never a set it and forget it deal. Also, it's imperative to test the backups once you've implemented your backup strategy.
They will increase somewhat in the coming year or two, but probably not by a great deal. In any case, I'm the only show in town. Whatever happens on the machine goes through me, so I have no trouble keeping on top of what's happening with it.
If you're wondering why, here is a great read:
Yes, that's a good article. I also attended SQL in the City in London this summer, where this sort of stuff was addressed. Grant Fritchey also has a good video tutorial about it here: http://www.red-gate.com/products/dba/sql-backup/version-7/#dbateam