May 29


Why Do I Want You to Sort Your Backups?

By Charles

May 29, 2017

Database Services, Server backups, sql server

There are so many companies that are not doing their database backups correctly but they need to be sorted asap. I can’t do them all on my own so I need your help to sort them. So let’s look at how to sort your backups.

How many backups do I need to be doing?

For something which is so important to a business, well vital really, it is a surprise to see backups failing or entire backup processes just not fit for purpose. With the massive cyberattack happening, last weekend NOW is your last chance to sort them before something really serious happens.

database backups

There should be a central solution for your database backups. Having one place to look and check your entire business backups have completed successfully is a high priority. It is of course not always possible to have just one piece of software looking after everything so you need one dashboard pulling in multiple data sets which can give you that single pain of glass to help you sleep at night.

There should be three copies of your data at any point in time. If you do not have 3 copies you DO NOT have ANY data. You should therefore have your live copy of your data. A backed up copy on site and an off site copy of your data. This is really the bare minimum your business needs to recover from a disaster. I would prefer two off site copies at separate locations. Only one would need to be accessible for a disaster but all it takes is an accident to burn a place down and a system to fail for your business to go up in smoke.

Hot and Cold

We talk about hot and cold systems. A hot system is one you can use straight away in a disaster situation. A cold system is one that needs some time to be brought into a usable condition. So you may have a server setup for the application but it requires a fresh restore from your most up to date backup to bring it online with minimal data loss.

When did you last test a DB restore?

Test restores should be performed on all DBs regularly, once a week should be sufficient although this should be happening fairly regularly anyway if you are refreshing dev/test from production servers for testing.

Why test restores? You are checking that the backup file you are creating is readable otherwise what’s the point of even having a backup? I can assure you there is no worse feeling than thinking you have backups and can restore in any situation to find out that when that situation occurs your backups count for nothing.

If you are not regularly testing restores from production systems you have NO backups. That sense of security counts for nothing without being tested. If you want to sleep well sort your backups.

Run DB Integrity checks

Whilst not a backup process at all you should still be doing this.

Run DBCC integrity checks on live databases. 2 types of checks full at the weekend as you have more time and physical only during the week for a quicker check. You do not want to be in the position where you must restore the DB due to internal file corruption (it happens and its a &*%$£ to recover from).

You also do not want to be in that position and realize that your backups failed and you are unable to restore from your backup solution. I am talking from experience, we managed to recover the data but it took several days of a complete systems outage to accomplish.

Full, Differential and Log backups

Before you can sort your backups you need to know your backups. There are three types of backup in SQL Server.

A full backup writes everything to disk. It is an exact copy of your data in a new file which can be restored somewhere else. It also starts off the backup chain.

A differential backup is a backup of all the changes that have happened since the last FULL backup. You can take multiple differential backups in a chain but you will only ever have to restore the most recent differential after the previous full backup. So if you take a Full on Sunday and differential backups every night Monday to Saturday. If you need to restore back to Friday evening you would just restore the Sunday backup and the Friday evening differential no need to restore all of the diff backups from Monday to Thursday too.

Log backups for a point in time restores can only be taken if the DB is in FULL RECOVERY MODEL. These backups allow you to backup each transaction that happens on a DB. In order to restore to a point in time, you must have a DB in the FULL recovery model and be taking log backups. If you do not take Log backups the log will be constantly growing until the next log backup is completed or the drive runs out of space.

Please sort your backups

Right now task a member of your team or your company to design a backup schedule/solution. If you do not have anyone with experience or you prefer to get someone in give us a call. We will go through the whole design of a backup strategy based on your business requirements. We take into account disaster recovery and business continuity in order to keep your business safe. So take the time right now to sort your backups.

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