Where to store your backups
When you do a backup, whether it be full, differential or log, SQL creates a backup file that you have to store somewhere. You’ll need to decide for yourself where to store your backups based on your requirements for restoring. To work out where to store a backup, we need to work out what we’re protecting against. Once you know what to protect against, we can work out where not to store your backup, and then evaluate the remaining options to pick the best place to store it.
Let’s go with a simple option – you have a server that you can afford to lose a days worth of data on, so you plan to do a full backup every night at midnight. How are we going to make sure that this backup is available in the event of a server crash, a break in, a fire, a virus? If you store it on site, are you protecting against a fire? No, so then you can look at your offsite options – maybe ship it to another data center that you have servers in, maybe to the cloud, maybe on a tape down to the local bank vault. It’s a nice simple option, isn’t it? And for some scenarios, that’s going to be a good enough solution.
Backup location is a compromise between ease to backup vs ability to access it in a disaster.
Storing the backup on the local server is easiest to setup and troubleshoot, and it’s usually fastest to store the backup on the local server. However, having it stored locally provides very little protection against most disasters – disk crash, server not booting, power surge, building destruction.
Having the backup stored further away from the database makes it safer, but it will take longer to do the backup and complicate the process. What if the network connection goes down and you can’t connect to your backup location? How do you make sure that physical media is taken offsite every day? How long does it take to write to your backup location? Is the data corrupted on the way? What access do I need to configure on the network share? Even having the data offsite may not be enough. What happens if a virus or ransomware hits your network – can it cross from your primary location to your backup location? Maybe you still need disconnected media to protect against some disasters.
In an earlier post I said that RPO can be different for different types of disasters – maybe if your building burns down, your RPO is 1 days worth of data, but accidental deletions need to be restored to within 15 minutes. Your backup location will be dictated by your RPO objectives. Maybe you need to store your nightly backups offsite, but your log backups can be on local network storage.
So where should you store your backups? You’ll need to work out the requirements for your data. How far from the database do you need to store the backup to mitigate the risk? Start at local storage and move further away until you find a point where the risks are mitigated and you’ll be able to meet your RPO.