I have 10gb RDBMS (SQL Server). I would like to back it up as often as possible, but it seems that I will need an awful lot of space to make this happen on the order of several times per day.
How often is reasonable to backup a DB this size? How are the backups normally stored?
I looked into Amazon S3 and am wondering how common is it for people to use a service like this to do their DB backups?
This question came from our site for system and network administrators.
Sounds like you don't understand how backups in SQL Server work. There are three different kinds of backups in SQL Server.
FULL - Full database backups dump the entire database and the active transaction log to a single (or striped, but lets ignore this feature for now) file on disk.
DIFFERENTIAL - Differential database backups backup all the data which has been changed since the last full backup to a backup file. To restore from a differential backup you MUST have the full backup which was taken before the differential backup was taken.
LOG - Log backups backup just the data which is currently in the transaction log. Log backups give you the ability to restore the database to a specific point it time (for example if someone dropped a table you could restore to a point just restore the table was dropped and get back all the data from that table). LOG backups require that your database be in either FULL or BULK_LOGGED recovery mode. To restore log backups you have to have the most recent full backup taken before the log backup you want to restore, optionally the differential backup taken between the full backup and the log backup, and all the log backups between either the full or differential backup and the last log file you want to restore to.
From what you have described it sounds like you need to loose as little data as possible. You'll need to change the recovery mode to FULL or BULK_LOGGED (FULL is what most people select) and then setup daily full backups and transaction log backups every few minutes. The frequency that you run the log backups will depend on how much data you can afford to loose. The business should be the one that sets this number, not you. It isn't uncommon to run log backups every 10-15 minutes depending on the requirements.
After the backups are setup, you'll want to configure something to delete the files after some number of days (depending on the business requirements and how much space you can afford to store).
You'll want to backup the databases to a server in another EC2 availability zone so that if a repeat of the Amazon outage from earlier this year happens your backup data is still safe.
It might be worth it to bring a consultant in for an hour or two to make sure that your backups are done correctly. If they aren't done right, now is the time to figure that out, not when the system has failed.
I think the main question is not how often other people back up their databases - but what level of loss your business will accept. If your server dies will they be happy going back to a backup file from 24 hours ago or will they need one from an hour ago?
All our production databases do a full backup once a day, they then run transaction log backups every hour. In the event of a complete failure our worst case scenario is that we lose an hour of data (although in fact we use database mirroring to protect ourselves against even this). Transaction log backups also give you the option to do point-in-time restores: http://msdn.microsoft.com/en-us/library/ms179451.aspx
Another factor is the recovery model of the database - if you're in full recovery mode then you'll need transaction log backups to stop the the transaction log becoming unmanageable. By contrast, if you're using simple recovery then transaction log backups are not an option.
This is a decent starting point: http://msdn.microsoft.com/en-us/library/ms191239.aspx
Hope that helps...
What version of SQL are you working with? What does "as often as possible" mean? Why do you want to do this?
You don't have to take Full backups. You can do a once weekly full, and a differential every night. With t-sql or SSMS you can have the backups auto deleted after n-number of days thereby keeping your backups neat and tidy.
As often as possible? Why not transaction log backups every 5 minutes, then delete them every 5 days?
::I have added more questions::
Lets say your have a common business scenario with your busy time being: 9am-5pm Monday-Friday. Then I would suggest: Full backup at Sunday night. Differential backups at 8 am, 6 pm and 1 am (to reduce the recovery time). Log backups every hour or depending on what your business requires.
Depending on your retention period, you should have an automatic cleanup job to clear out the old backup files.
You should store your backups on some form of redundant disk (mirrored).
If you use Amazon S3, then you will have to pay for the bandwidth, storage and get/put requests. Then there is a further complication if your DB stores sensitive data. You can instead get an external drive or NAS device and store your backups on these. Price should not be an issue as disk is cheap.
