DEV4 Blog

Backing up your database may not be enough to ensure you can respond to a Disaster Recovery incident.

For the purposes of this article, we'll use MS SQL Server without replication, as our working example.

Backing Up Your Databases

Choose a Separate Location

By default, SQL Server backs up your data to the local SQL Server installation folders located on your C:\

If the server fails, your database fails and you may not also be able to access your database back-ups.

Always set your server databases to back up to another location or server.

Health Checking Databases

Performing physical health checks on your database back-ups provides a secondary level of protection from data loss.

Physical checks in this sense refers to obtaining a database back-up, restoring it locally - or not on the original server - opening key tables to perform a record count and keeping a log of your checks.

For example;

Last week:

tblEmployee held 100 records.

This week:

 tblEmployee holds 110 records.

By restoring your database locally or on another server, you also have an additional copy in case you need to restore your Production database from a point in time.

Another option? Replication

Another method of ensuring DR compliance is to set up SQL Server replication.(SQL Server replication is a whole subject on it's own - but to learn more, see this overview).

Under the SQL Server replication method, data and database objects can be continuously copied to another server database - thus ensuring you have a DR position database in the event your primary server fails.

How Often Should You Health Check?

The level of physical check depends on your organisations tolerance to data loss. Typically, a seven day interval is sufficient - meaning that you are at risk of losing a maximum of 7 days data.

The interval is subjective and also dependant on the criticality of the system you're checking. Heavily utilised databases may require more frequent checks.

Many external tools and inbuilt SQL procedural checks are available to carry out the above but if you're looking for a vanilla flavoured approach to database QA, the above works well.

DEV4 carry out database health checks on all Client hosted databases at various intervals. 
You can't replace lost data but you can minimise & mitigate your organisation's exposure to losing information.

Published on 26 Feb 2019 by Dev4

Add new comment...