April 29


When’s too Late to Sort Your SQL Backup strategy?

By Charles

April 29, 2016

Database Services, SQL backup strategy, sql server

[h2_heading]Do you have a SQL backup strategy in place?[/h2_heading]

You have at most, between now and the next disaster situation to sort out your backup strategy, the catch however is that you will never know when that disaster will strike. So you really only have one option. Sort your SQL backup strategy now once and for all.

SQL database backup

I personally can not sleep knowing that a SQL backup strategy is either non existent or worse not fit for purpose. False hope is no hope at all.

[h2_heading]Are there any rules for a backup strategy?[/h2_heading]
There are certainly things to remember here are three of the things I think about.

  1. If you do not have 3 seperatly 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 backups
  3. The business leads when it comes to determining acceptable data loss / recovery times

So what do I mean by 3 separate copies. Well you have your production data which is one copy of your data but that’s not really a backup it’s your live system. A local backup to a backup drive would be considered one copy. Doing 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 are then stored offsite or by a third party. These 3 copies must be in place for every single days backups and at least one copy must be off site.

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 restored data then you might as well not take the backup 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 on 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 to be. It resembles Azkaban and you feel like you are receiving a kiss from a Dementor. You end up having to go back to an even earlier backup and lose some data then try to recreate what has been lost. Do not end up in this position just test your restores its much easier.

Contrary to popular belief the DBA does not choose the data loss and recovery times of a system. 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 the recovery inside both the RPO and RTO.

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

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"}