Why I Want YOU to Sort Out Your Backups

By Charles

May 29, 2017

Database Services, Server backups, sql server

So many companies are not doing their database backups correctly, but they need to be sorted asap.

I can’t do them all alone, so I need your help sorting them out.

So let’s look at how to sort your backups.

How many backups do you need to be doing?

For something so essential 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 out before something serious happens.

There should be a central solution for your database backups.

Having one place to look and check your entire business backups have been completed successfully is a high priority.

It is not always possible to have just one piece of software looking after everything, so you need one dashboard pulling in multiple data sets that can give you that single pane of glass to help you sleep at night.

There should be three copies of your data at any point in time. You DO NOT have ANY data if you do not have three copies.

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 the bare minimum your business needs to recover from a disaster. I 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 Systems

A hot system is one you can use straight away in a disaster situation.

A cold system 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 your database backup?

The test should be performed on all DBs regularly; once a week should be sufficient. However, this should happen reasonably regularly anyway if you are refreshing dev/test from production servers for testing.

Why was the test restored? You are checking that your backup file 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 your backups count for nothing when that situation occurs.

You have NO backups if you are not regularly testing restores from production systems.

That sense of security counts for nothing without being tested. If you want to sleep well, sort your backups.

Run database integrity checks

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

Run DBCC integrity checks on live databases. 2 types of statements complete at the weekend as you have more time and physical only during the week for a quicker review.

You do not want to be in the position where you must restore the DB due to internal file corruption (it happens and is a pain to recover from).

You also do not want to be in that position and realise that your backups failed and you cannot restore from your backup solution.

From experience, we managed to recover the data, but it took several days of a complete systems outage.

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 that can be restored elsewhere. It also starts off the backup chain.

A differential backup is a backup of all the changes 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 take a Full on Sunday and differential backups every night from Monday to Saturday.

If you need to restore to Friday evening, you would fix 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 back up each transaction on a DB.

To restore to a point in time, you must have a DB in the FULL recovery model and take log backups.

If you do not take Log backups, the log will constantly grow until the next log backup is completed or the drive runs out of space.

Please sort your backups

Right now, task a team or company member to design a database backup schedule/solution. If you do not have anyone with experience or prefer to get someone in, call us.

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 to keep your business safe.

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