Why Backup SQL Server?
I’m going to start a new series on backing up SQL Server. This was inspired by @Kendra_Little in one of her Dear SQL DBA podcasts
In this series I want to work through why we need backups, how to do backups, and the different types of backups that you could do and why you would use them.
To start the series, why bother doing backups in the first place?
In my mind there are 5 primary reasons for doing a backups:
Disasters
This is the most obvious one. Backups are super important for business continuity in the event of a disaster. Imagine if all of your customer data, all of your stock data, all of your account balances and so on burnt to the ground in a fire, or were destroyed in a flood or were blown away in a cyclone. Unlike physical property, there is no reason why you can’t have a backup of your database stored offsite – in a bank vault, in the cloud, in somewhere that is physically separate from your primary building. It’s much easier for a company to survive if it looses a day or two worth of customer data compared to losing all of it forever. Although the chance of your building being destroyed is low, the implications are huge – all the way through to bankrupting the company and facing huge legal action – imagine if your bank lost all of its accounts data.
Server Crash
Very similar to Disasters, a server crash is like a very locallised disaster. In the virtual world, server crash could mean that you can’t boot your VM, your physical server is down, your SAN is broken and so on. Server Crashes can usually be mitigated by a High Availability solution, such as Always On Availability Groups or Mirroring, but if you can have a long enough down time to restore data from backups, this is often a simpler solution. Although becoming rarer with modern hardware, server crashes are still a major risk.
Corruption
Once the data is written to SQL, we need to make sure that the data stored on disk doesn’t become corrupted. Even with modern disk drives and RAID controllers, data can still become corrupted and unrecoverable – whether it be from a hardware failure or a software crash, once the data is gone a backup can be the easiest way to get it back.
Data Loss
This is the one that scares me the most – the accidental delete or update statement that breaks all of your data; the person who deletes the wrong virtual disk on the SAN; the drop database statement from someone thinking they are on a test server but are actually on the production server. The reason this one scares me is because it’s the one that I’m most likely to do by accident. It’s much more likely for me to run an update statement and miss the where clause, than it is for me to set fire to a building. If someone deletes all of your data, how are you going to get back? Even if you have some sort of replication, that delete is going to be replicated, probably before you have a chance to stop it. This is one of the biggest reasons why you need backups, even if you have Availability Groups, Mirroring or Log Shipping.
High-ish Availability
Microsoft current offers 2 High-Availability Solutions – Always On Failover Cluster Instances and Always On availability groups. Also still supported is database Mirroring. A solution often skipped over is Log Shipping. Log Shipping is where you restore your database to your standby server using a full backup once, and then on a regular basis you do a log backup of your primary and restore it on your standby server. Although not a hugely important reason to do backups, it’s a very beneficial side effect from it.
As a side note, Log Shipping is actually one of the simplest means of having a warm standby server ready to go when disaster strikes. It’s not a high availability server in the sense that it can automatically failover and you can be sure that all transactions are committed on all servers, but if you can have a few minutes of downtime and afford to loose a few minutes of data, then Log Shipping could be the simplest option out there.
Moving Data
Finally, moving data. Businesses often have a need to move databases from one place to another. This could be due to moving to a new production server, or needing to refresh a test environment, backups are a simple way to ship the copy of the database from one server to another (or even back to the original with a different name).
In my mind, these are the top reasons for doing backups. Are there other reasons that you can think of?