Why You NEED A SQL Backup Strategy

By Charles

April 29, 2016

Database Services, SQL backup strategy, sql server

Do you have a SQL backup strategy in place?

You have, at most, to sort out your backup strategy between now and the following disaster situation; the catch is that you will never know when that disaster will strike.

So you only have one option; sort your SQL backup strategy now – once and for all.

I can not sleep knowing a SQL backup strategy is non-existent or unsuitable. False hope is no hope at all.

Are there any rules for an SQL backup strategy?

There are certain things to remember. Here are three of the things I think about.

  1. If you do not have three separately stored copies of your data, you do not have any data.
  2. If you do not regularly do test restores from backups, you do not have any stoppages.
  3. The business leads when determining acceptable data loss/recovery times.

So what do I mean by three separate copies?

Well, you have your production data, one copy of your data, but that’s not a backup. It’s your live system.

A local backup to a backup drive would be considered one copy. A flat file backup of these backup drives would be a second copy. This might be to disk on another SAN.

The third copy would be having that SAN replicate to another SAN offsite or taking a copy and putting them on tape, which is then stored offsite or by a third party.

These three copies must be in place for every day’s backup, and at least one copy must be offsite.

Best Practices for SQL Backups

Regularly test restoring a copy of your systems from your backups.

If you do not know that you can restore your backups and use the converted data, you might as well not take the jam in the first place. Taking a backup is just half the battle and can give you a false sense of security as you check the backup software status, and it says everything is green, so you believe it.

Everything continues until a production system requires a restore to roll back a failed change when you find out the backup cannot be restored due to an error.

I have been there, and it is not a nice place.

It resembles Azkaban, and you feel like you are receiving a kiss from a Dementor. You must return to an earlier backup and lose some data, then try to recreate what has been lost.

DO NOT end up in this position!

Just test your backup restores – it’s much more accessible.

Contrary to popular belief, the DBA does not choose a system’s data loss and recovery times.

Both the RPO (Recovery Point Objective) and RTO (Recovery Time Objective) must be decided by the BUSINESS.

The DBA will then implement a backup strategy to enable recovery inside the RPO and RTO.

Hopefully, after reading this, you won’t have trouble sleeping. As always, contact us if you want to discuss a SQL backup strategy,


About the author

Microsoft Certified SQL Server DBA with over a decades experience including work for large FTSE 250 companies amongst others. The SQL Server stack has been the focus of almost all of my career in IT. I have experience designing, supporting and troubleshooting large Data Platform deployments.

You might also like

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}