November 3, 2011 at 10:49 pm
The current size of the bak file generated from my database is 6MB so it's a small database. The current time to execute a backup is < 2s.
I don't expect the size of the bak file to grow to more than 100MB.
I've read that doing a full backup can have performance implications. Is this because the process uses a lot of server resources or does the process implement additional database locking?
Since the total time to do a full backup is < 2s I'm wondering if I should just schedule hourly full backups for now. What are your thoughts on this?
November 4, 2011 at 5:23 am
How much data is likely to change over the course of an hour? Is data being inserted/updated/deleted frequently during the day, or is it fairly static? How many insert/update/delete operations can your business tolerate losing, if something goes wrong and you have to restore to the most recent backup? Then answers to those questions will help determine your backup strategy and recovery model.
Some examples: if data hardly changes during the course of a day, just stick with nightly fulls. If the data is static most of the time, but an automated process dumps new records in at 10:00 AM and again at 3:00 PM, you might want to schedule a full backup for 10:30 AM and 3:30 PM and leave it at that. If customers are modifying, say, 5 rows of data per hour on average, the original information they are entering also exists on paper somewhere, and entering that much data by hand takes about 2 minutes...you can probably get away with backing up once or twice a day. If this is a busy system where 1000 rows of data experience change over the course of an hour...well, you may want to switch to FULL recovery model, take a full backup every few days, and take transaction log backups every 15 minutes.
Those are just a few scenarios to get you thinking; the main point I'm getting at is, start by defining how much risk your business is willing to be exposed to, along with the data change patterns for this database, and that will give you a good idea as to what your backup strategy needs to be.
November 4, 2011 at 9:07 am
+1 with what JonFox said.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply