I have 15 customer databases that have minimum replication at different sites. I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip. We recently had a hard drive crash at 6PM which means we lost all data in the database from 2AM – 6PM that day. How can I back-up our databases throughout the day without compromising the functionality of the database during the day? We do over 1,000 transactions daily so we are constantly writing to the database. My current method is not realistic for hourly back-ups as performance would suffer on the machine.
Sql-server – How to backup databases to ensure data recovery
backupsql-server-2005
Related Question
- Mysql – Backup and off-site restore setup
- Postgresql backup and recovery
- Log (LDF) File Grows Continuously – SQL Server Backup and Log Management
- Sql-server – Help to understand ‘log space used’ metric
- Mysql – How to better backup MySQL 5.6 database
- Sql-server – why are the Database backups occasionally failing
- Restoring Database with Backups on Outdated Server Snapshot in SQL Server
Best Answer
I am noting the other answers here, but I don't believe any of them (at this moment) is a complete answer / solution to your problem. Yes, you can backup to a remote location, but that is not always the best way to do it. Backing up to a network location can be murderous to your bandwidth.
Transaction Log backups is very important and is the right way to go, and they can be off-setted with differentials. But there is usually some theory and planning that needs to go into your implementation thereof.
Differential and Transaction Log backups should cause a lot less performance issue for you, as the backups are a lot smaller. Differential Backups only contain changes made since the last full backup, and Transaction Log Backups only contain transactions done since the last full, differential or transaction log backup (normally just a few MBs).
Based on the details you have provided, there are over 1000 transactions done per day. On a normal 8 hour work day, this calculates to just over 2 transactions a minute.
Before you implement any backup plan, you need to determine (as a business) what your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) is.
RTO relates to the maximum amount of time you are willing to lose during a disaster, to restore normal operations.
RPO relates to the maximum amount of data that you willing to lose during a disaster.
In most cases, the lower the RTO, the higher the RPO and vice versa.
With the metrics above, you should then decide what the best backup plan is to implement.
For e.g.:
PLAN A: (Low RPO, i.e. minimal data loss)
Transaction Log Backups every 10 minutes
No more than 10 minutes of data will be lost, but the restoration process might be complexed and therefore time-consuming (close to 30 backup files to restore from)
PLAN B: (Low RTO, i.e. minimal downtime)
4 Hourly Transaction Log Backups
Up to 4 hours of data could be lost, but the restoration process would be faster, with only a max of 4 files to restore
Importantly, you should note that if you back up your databases to the same drive as where your data is, and you have a catastrophic drive loss, you are likely to lose the backups too. Always have your backups on a separate drive from data and logs.
Suggestions to improve / edit this answer is welcome!