Backups, Data Verification and Data Integrity

These are tips for the general care of any database, be it Theatre Manager, your accounting information, Word or Excel documents, contracts, or any other data on your computers. There are three sub topics:
  • Backups
  • Data verification and integrity within the database
  • Computer checkups

Backups

American Express estimates that the average value of 100 megabytes of company data is about $1,000,000 if you count the cost to acquire it, or damage to the company if you lost it all. They also state that 50% of companies that do not recover their data within 10 business days never fully recover financially. Backups are an important safeguard for a valuable corporate asset.

Media Use a commercial grade media to store the information. This can be a replication server, removable media, memory sticks, disk, or cloud-based backup
Backup Data Backup all data to one common place using one common process. If data is categorized and treated differently, it increases the possibility that data can slip through the cracks.
Backup Programs Too! Simply backing up data is not sufficient. Programs that were used to create the data also change over time and there is no guarantee that data from a few years ago can be read by the current version of a program. The best backup strategy includes backing up an image of all data and programs to one source so that both can be restored without a synchronization issue (i.e. now I've got the data, where is that program disk - they can be lost, the floppies go bad, or the serial number activation codes for some programs are lost - all of which mean that you would recover the data without the ability to use it).
The Other Computers The need for Theatre Manager database backup is obvious.

Including operating systems, programs and machines can also be important. The actual OS for each machine (Mac or PC) is different and optimized for the particular machine during installation. (For example, older machines often cannot run newer operating systems and vice versa).

There are serialized components within the operating system for network oriented multi-user applications (e.g. Theatre Manager, backup software, Quark Express, Word, Excel) that need to be restored at the same time as the program in order to work.

Fortunately, Apple includes a tool called Time Machine - which is worthwhile implementing for all data and all computers other than Theatre Manager's database server. On Windows 8, there is a feature called File History that mimics some portions of Time Machine. (There are also other third-party products for Windows).

Retention Strategies One backup is not good enough. Your backup scheme should include generations of backups going back a number of months or even years, depending on legislative requirements or corporate risk. Typically, you should have:

  • 2 to 4 weeks of daily backups. This is the most critical time period as files are usually lost or file corruption is noticed within this time frame.

  • There should be one or two years of 'month end' backups that occur on the first day of the month. This allows recovery of data to a specific point in time (e.g. what was the accounting data at the last grant application, or what were the house counts in Theatre Manager, or recovery of an important graphic from last year's brochure that should be used again).

  • Ultimately, we suggest you save 7 years of yearly backups to satisfy government requirements, should they come looking for data (don't forget that you need the program at the same time as the data).

This represents 30+12+7 = 49 distinct backups

Offsite Storage Backups need to be rotated off site - out of the office. This allows business recovery in the event of fire or theft from the principal site. Removable media or automated FTP of databases can be used.
Redundancy
Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.
Devices can break. You should have access to another similar device so that you can restore backup files and resume operation until the primary server device is repaired. For a moderate cost, you can have an immediate redundancy for the database server via hot streaming replication measures.

Example Backup Strategy and Devices

Volume of Data The size of most databases we see these days is under 500 MB, although some are 3 or 4 GB.

A rule of thumb is that many backups are far more valuable than the cost of the backup device. A 1Tb SSD backup disk is worth the investment - consider it insurance.

Performance A snapshot of the database should be done at least once a day, if not more often.

Alternatives
Streaming Replication This involves a second database server computer to have a live-hot backup of the database. This is highly recommended for active sites that need to minimize downtime or service outages.
Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.
Removable Media Examples are USB, FireWire, or Thunderbolt external drives which are useful for offsite rotation. Simply copy the database from the backup directory to the removable device and take it with you.
Automated Transfer

Examples are FTP, cloud-based backup, Dropbox, or other services. These can be set up to automate the process of saving an offsite copy of the database.

Testing Your Backups for Integrity

Data Integrity in your backup is important.

Just having backups is a very good start.

We also recommend doing tests on any important data according to a schedule. For example, with your accounting data, printing a balance sheet or income statement monthly and then testing the receivables balance is a generally accepted accounting principle. What you are doing is really testing the data integrity.

Theatre Manager has some built in safeguards. We recommend:

  1. Scheduling the backup process one or more times daily.
  2. Looking at the backup sizes daily to see that they are generally similar or growing in size as you use the system. TM will alert you if a backup is missed or is smaller in size than the previous one, indicating a possible problem - but do not rely on this as your only visual test of backups running properly.
  3. Using
    • AMS Offsite Backup storage to automate sending data securely offsite -or-
    • making copies on removable media and on thumb drives and taking some offsite manually
  4. Restoring your backup database periodically to ensure that it can be restored.

Do's and Don'ts of Backups

The do's of backups:

The don'ts of backups:

  1. Do backups daily
  2. Do backups before an upgrade to your operating system
  3. Do backups before doing a significant batch activity (like merging of patron records).  It is sometimes easier to back out a change by restoring an older database!
  4. Keep two weeks worth of daily backups
  5. Keep 2 months worth of monthly backups
  6. Keep a yearly backup of the database.  At the same time, back up the specific copy of Theatre manager used to access the database.  If you upgrade later in the year, it means that you can still access your old database using the old program without having to upgrade that database too.
  7. Test your backups once a month by trying to restore from the backup to make sure they are reliable.
    • See if you can read the data.  If not, your hardware may have problems that need fixed.
  8. Do take a copy of your latest backup home with you.  If your machines are stolen or wiped out in a fire, the backup at home will be your only friend!
  1. Do not do backups to the same drive over and over again unless you are also backing up offsite.  All it takes is for a bad copy to be made and the computer die to realize that you have nothing.
  2. Do not skip a day
  3. Do not ignore backup error messages from the computer that say backups didn't work!  Find out why and then get the hardware fixed if need be.
  4. Do not do backups unreliable media.