Intended audience

As an ERP vendor for small and medium-sized manufacturers, we see a wide variety of tactics when it comes to managing information technologies. Some companies have a dedicated IT staff whose sole responsibility is keeping all servers, clients, networks, applications, and other related systems up and running. These companies typically also have a database administrator who insures all critical databases are properly backed up and maintained. However, we also commonly see manufacturers put less emphasis on IT. In other words, someone has been designated as the “IT” person, yet that is not their primary role.

In this article, we’re addressing the companies without dedicated IT teams or people because this is where we are more likely to find critical deficiencies in database backup strategies. We’ll highlight the basic fundamentals of backup and restoration procedures that should be followed, so you can avoid a catastrophe like a software virus infection or hardware meltdown.

Related: ERP Database Design: A Guide for Non-Technical Managers

Related: What is Infor CloudSuite Industrial?

Step 1: How much are you willing to lose?

This question reminds me of an old song by David Bromberg: “A man should never gamble more than he can stand to lose…” While I may have just lost the younger crowd, this rings true when discussing database backups.

How much data would you be willing to lose? The obvious answer is none. But in reality, you should have a strategy that defines the maximum data you would be ok losing—and the time you would need to re-create that data. In a transaction-intense environment, this should be a smaller interval of time.

Note: To keep things simple, we’ll use Microsoft SQL Server as our example throughout this article, but most database platforms will have the same capabilities.

Step 2: What should you back up?

Once you define how much you’re willing to lose in your database backup strategy, you then need to develop the backup process to ensure you can achieve that. A transactional database such as Microsoft SQL Server has two files to back up: database and log. Speaking at a high level, the database contains all of the structure, and the log file captures the transactional data throughout the day. Think of the database as the static file and the transaction log as the incremental file. 

The transactional data is copied to the database during the log file backup, which also truncates the log file to a minimum size. However, the backup itself is saved, so if the original database file happens to be erased or contaminated, you can restore its backup and then each incremental log backup. In short, doing regular log backups allows you to restore to a specific date/time. The risk occurs in the time between log backups. For example, if you back up logs every hour, then you risk possibly losing an hour’s worth of data. The more often logs are backed up, the smaller the log file is, so it has a smaller effect on performance.

You also have the option to perform a “simple” backup strategy in SQL Servers. This allows you to back up the database and log files together, as one backup file (which will also truncate the log file). This is a common option for smaller companies that feel they can take the risk of losing up to a day’s work if they only back up nightly.

Related: IT's Roll in ERP: Before, During & After the Selection Process

Step 3: Where do I back up to? 

When it comes to storing backup files, you can never be too cautious. The initial backup files can be saved to your local server drives or a tape drive, the latter being safer due to separation from disk failure on the server. I would suggest backing up to both, and also having a cloud storage provider you can immediately copy the backup files to.  Getting the backups off-site is the safest way to prevent loss.

Step 4: Restoring a backup

I have come across many instances when a company has been backing up its database on schedule, yet when it has a failure and needs to restore the backup, it fails. Just because your database and log files back up successfully does not mean you have a successful backup! 

Create a routine to restore the backup files to a TEST, or temporary copy of the database, to prove that the backups can be restored—this is part of database backup strategies best practices. In a perfect world, this should be done daily. For a small company with no IT staff, this is unlikely to happen daily, but should be on your radar to do periodically. The decision goes back to the original question—how much are you willing to lose? Doing log backups every hour gives the ability to restore at least back to an hour ago, but if your backups are corrupt, your last restorable backup will be the last one you tested the restore process with. Keep that in mind when coming up with an overall strategy for testing the restore.

Conclusions

Whether you have a dedicated IT staff, person responsible for database backups, or part-time “IT” role for someone in your manufacturing organization, a database backup strategy is imperative to preventing potentially disastrous data loss. Don’t become a statistic!

Questions? Visual South, a full-service Infor Gold Channel Partner ERP provider, can help guide you toward the right solution that fits your business.