We’ve been talking about using Database Maintenance Wizards to create disaster recovery implementations through database backups. If your new to managing SQL databases this probably makes a lot of sense to you. You backup your database. If a disaster happens, you restore the database after hardware becomes available.
In our comments there was a lot of talk about why we need to do Transaction Log Backups. The key reason is the granularity of data recovery. If you do a transaction log backup every five minutes, the most data you could ever lose when a database fails is five minutes. It could be a little time consuming to restore from many transaction log backups. For that reason, we intersperse transaction log backups with a new baseline of a Full Database backup on an infrequent basis, and a Differential Backup more frequently.
The thing that was lost in many of the comments is the definition of a disaster. In a database, a disaster is anything that corrupts or disables a database. This could be caused by a bad disk controller, a bad disk, server dying, or even worse, bugs in software or hacking. When the latter happens, your database may still be online, but the data is not trustworthy.
If you don’t have a backup at different points in time, how do you get back to a time before the data corruption occurs?
Microsoft has the “Always On” technology that works by using two or more instances of the same database with failover to a secondary database should the primary database go offline. The change over is pretty seamless, and depending on the number of entries in the transaction log, your application may never even know a change occurred. SQL Azure uses this implementation, and it is very reliable.
I remind you again that the “Always On” implementation cannot help you for those extremely rare instances when you are hacked, or corrupt your data through bugs. When that happens, the only recourse you have is some sort of database backup. I have experienced these kinds of data corruption failures only three times in three decades. So, how important is it to cover this risk? You’ll have to answer that question for yourself.
Denny Cherry once wrote a blog on what happens when you have to move to your failover location years ago. Think about that. What will you do if you lose your entire data center? That has happened more often than data corruption. Having database backups offsite, and ready to go at a moments notice can be a big asset when you lose your entire server. Without that, you’re just lost.
I hope this clarifies why I take the time to review an old technology for database backup and restoration. It doesn’t have to be your first line of defense. It may need to be your last line of defense.
Cheers,
Ben