Backups, Data Verification and Data Integrity

This tip is about 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 the percentage of companies that do not recover their data within 10 business days which never fully recover financially: 50%. Backups are an important safeguard for a valuable corporate asset.

Media

Use a commercial grade media to store the information. Tape, disk and optical media are listed in order of preference due to longevity. Tape has a 3-5 year expected life, optical has a 10-25 year expected life

Floppy disks are an unreliable backup media because:

they have an expected life span of less than 2 years (industry average)

there is just too darn many of them that the sheer volume of floppies create a management and storage problem

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).

All Computers

The need for data backup is obvious. Including operating systems, programs and machines is also important. The actual O/S for each machine (Mac or PC) is different and optimized for the particular machine during installation. (For example, SE/30's run better with system 7.1, new PowerMac's need 7.6.1, 486's with low memory run better with win 3.11 and new Pentium's may have Win95 or NT).

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

Rotation of Media

Optimal rotation for backups include a scheme that supports generations of backups going back a number of months or even years, depending on legislative requirements or corporate risk. A typical, suggested rotation is:

2 weeks of daily backups that get rotated. This means there will be two Monday tapes which will get used each alternating week. These are the most critical tapes as files are usually lost or file corruption is noticed within this time period.

There should be 2 months of Friday backups. This allows files that slip through the first crack to be caught relatively easily

There should be a one or two years of 'month end' backups that occur on a specific 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 was the house counts in Theatre Manager, or recovery of an important graphic form last years brochure that should be used again)

Ultimately, a good scheme may include 7 years of yearly backups to satisfy the Government should they come looking for data (don't forget that you need the program at the same time as the data).

This represents 10+8+12+7 = 37 separate backup tapes or disks at a minimum. More will be required because repeated use of daily tapes will wear some out.

Offsite Storage

Backups need to be rotated off site - out of the office. Typically the most recent two daily backups are removed from the office the day after the backup is taken. This allows business recovery in the event of fire or theft from the principle site. Again, all data & programs on the same medium allows for faster recovery from the business outage.

Redundancy

In the case of physical backup device failure, it is important to own or have access to another similar device so that you can recover files or keep the backups proceeding until the primary device is repaired. Not everybody has this luxury and often take temporary measures.

Do's and Dont's

The do's of backups:

The don'ts of backups:

Do backups daily

Do backups before an upgrade

Do backups before doing a significant batch activity (like mass update of marketing fields, building season subscription favorite seats, adding a new database segment). It is sometimes easier to back out a change by restoring an older database!

If you leave something running over night (e.g. a big mail list rebuild or creation of subscription seats), backup the database first so your automatic tape backups will get at least one copy of the database.

Keep two weeks worth of daily backups

Keep 2 months worth of monthly backups

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.

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.

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!

Do not do backups to the same tape or zip drive over and over again. All it takes is for a bad copy to be made and the computer die to realize that you have nothing.

Do not skip a day

Do not ignore messages from the computer that say backups didn't work, or a Theatre Manager file was in use! Find out why and then get the hardware fixed if need be.

Do not do backups to floppies. They are unreliable. Use tape, digital tape, CD-burners, Zip or Jaz devices.

The do's of backups:	The don'ts of backups:
Do backups daily Do backups before an upgrade Do backups before doing a significant batch activity (like mass update of marketing fields, building season subscription favorite seats, adding a new database segment). It is sometimes easier to back out a change by restoring an older database! If you leave something running over night (e.g. a big mail list rebuild or creation of subscription seats), backup the database first so your automatic tape backups will get at least one copy of the database. Keep two weeks worth of daily backups Keep 2 months worth of monthly backups 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. 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. 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!	Do not do backups to the same tape or zip drive over and over again. All it takes is for a bad copy to be made and the computer die to realize that you have nothing. Do not skip a day Do not ignore messages from the computer that say backups didn't work, or a Theatre Manager file was in use! Find out why and then get the hardware fixed if need be. Do not do backups to floppies. They are unreliable. Use tape, digital tape, CD-burners, Zip or Jaz devices.

Example Backup Strategy and Devices

Volume of Data

Assume there are 5 machines on a network that need backed up. Assume each of these machines has at least a 250 meg drive (or more) representing over 1 gigabyte of files that should be backed up on a daily basis. Assume that the Theatre Manager database is on one of those machines and it may have a 500 Meg or greater drive. (Most new machines come with 1 meg minimum).

Compression algorithms (about 35% effective) mean that this example needs at approximately 1 to 1.5 gigabytes of storage for each backup.

Performance

The data is usually backed up and verified overnight. It needs to be done in a 10 hour slot from 11pm to 7am to avoid impact on evening call-outs, performances and morning work. Faster media is better.

Alternatives

Tape Units
(recommended)

Examples are Sony 7000 DDS-2 format (highly recommended), Exabyte tape drives, Travan format drives.

higher initial cost for device

lost cost of each media (tapes about $20)

store large quantities of data (4-8 gigs) which is very useful for sites with 3 to 15 computers.

Drives are available that will back up to 50 gigabytes or more.

Optical Cartridges

Examples are CD re-writable drives

moderate cost

low cost of media (about $10 each)

back up 650 megs per CD, useful for sites with one or two computers

media lasts as much as 25 years if properly cared for because it is optical storage vs magnetic tapes

Removable Cartridges
(least recommended)

Examples are Syquest or Zip Drives

lower initial cost

high cost of media (approx.. $150 for a 1 gig drive) times 27 for a good rotation.

more fragile that tape drives

need multiple disks if backups over 1 gigabyte. Useful for sites with 2 or three computers.

Data Integrity

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:

Running the data utilities check at the beginning of each day
This is very quick and will alert you if Theatre Manager noticed some problems with the network since the last time you ran the 'data utilities'. There is a setting in default data to remind you to verify the database. Please set it to 1 day.
Turn on create transactions at time of sale in default data
This adds protection by creating transactions when tickets are sold or donations are taken. The tickets and the transaction data are in separate data files so that if you lose one or the other (perhaps somebody deletes it), you can still recover a days activities.
Copying your database using the copy database feature once every month or so

This defragments the data inside the database and allows Theatre Manager to check all the data intensively. You can let it run overnight and then use the newly created database as from then on.

Computer Checkups

Sometimes computers just do bad things Windows 95 or 98 is notorious for that, Mac OS version 8.5 has some quirks as well, we recommend:

Platform

Activity

Frequency

Purpose

Mac
Run disk first aid. In the Mac's General Control panel, there is a setting to automatically do it if the system crashes. We suggest leaving on.

Weekly -Server

Monthly - Workstations

Verify that there are no file system errors on your mac that can cause it to crash. If errors are reported, please fix them.

Run Norton Disk Doctor and defragment the server's hard drive

Monthly

Speeds the access to the hard drive

Use Theatre Manager's internal database copy to create a new database.

Monthly

This reads the internal structure of the data and makes sure that all data is readable.

It also creates optimizes the internal structure of the database by leaving free space, consolidating data records and optimizes internal indexes. This speeds access to the data records

Windows
Under properties of a hard drive, there is a tab called 'tools'. One of those tools is to check the hard drive for errors. This should be done once a week on win 95/98 and can be extended to about once a month on NT workstation or server if there have been no problems noticed.

Weekly 95/98/ME

Monthly - NT, 2000 Pro

Verify that there are no file system errors on your mac that can cause it to crash.

Defragment the hard drive on the server

Monthly

Speeds the access to the hard drive

Use Theatre Manager's internal database copy to create a new database.

Monthly

This reads the internal structure of the data and makes sure that all data is readable.

It also creates optimizes the internal structure of the database by leaving free space, consolidating data records and optimizes internal indexes. This speeds access to the data records

Network
Use Theatre Manager's network test to examine your network for problems.

Semi Annually

There is a feature in Theatre Manager under setup->database utilities. In the 'db-utilties' pop up at the bottom of the screen there is an item called network stress test. If things just don't seem right on your network, you can run this to create a dummy database and simulate activity across the network. It may just point out a bad network card or cable

Platform	Activity	Frequency	Purpose
Mac	Run disk first aid. In the Mac's General Control panel, there is a setting to automatically do it if the system crashes. We suggest leaving on.	Weekly -Server Monthly - Workstations	Verify that there are no file system errors on your mac that can cause it to crash. If errors are reported, please fix them.
	Run Norton Disk Doctor and defragment the server's hard drive	Monthly	Speeds the access to the hard drive
	Use Theatre Manager's internal database copy to create a new database.	Monthly	This reads the internal structure of the data and makes sure that all data is readable. It also creates optimizes the internal structure of the database by leaving free space, consolidating data records and optimizes internal indexes. This speeds access to the data records
Windows	Under properties of a hard drive, there is a tab called 'tools'. One of those tools is to check the hard drive for errors. This should be done once a week on win 95/98 and can be extended to about once a month on NT workstation or server if there have been no problems noticed.	Weekly 95/98/ME Monthly - NT, 2000 Pro	Verify that there are no file system errors on your mac that can cause it to crash.
	Defragment the hard drive on the server	Monthly	Speeds the access to the hard drive
	Use Theatre Manager's internal database copy to create a new database.	Monthly	This reads the internal structure of the data and makes sure that all data is readable. It also creates optimizes the internal structure of the database by leaving free space, consolidating data records and optimizes internal indexes. This speeds access to the data records
Network	Use Theatre Manager's network test to examine your network for problems.	Semi Annually	There is a feature in Theatre Manager under setup->database utilities. In the 'db-utilties' pop up at the bottom of the screen there is an item called network stress test. If things just don't seem right on your network, you can run this to create a dummy database and simulate activity across the network. It may just point out a bad network card or cable

Remember, practice safe computing.

It doesn't take too long to do a daily 'quick verify', daily backup and a weekly check on your computer hard drive. Most of these things can be done when you leave for the night.

Media	Use a commercial grade media to store the information. Tape, disk and optical media are listed in order of preference due to longevity. Tape has a 3-5 year expected life, optical has a 10-25 year expected life Floppy disks are an unreliable backup media because: they have an expected life span of less than 2 years (industry average) there is just too darn many of them that the sheer volume of floppies create a management and storage problem
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).
All Computers	The need for data backup is obvious. Including operating systems, programs and machines is also important. The actual O/S for each machine (Mac or PC) is different and optimized for the particular machine during installation. (For example, SE/30's run better with system 7.1, new PowerMac's need 7.6.1, 486's with low memory run better with win 3.11 and new Pentium's may have Win95 or NT). There are serialized components within the operating system for network oriented multi-user applications. (e.g. Theatre Manager, backups software, Quark Express, Word, Excel) that need to be restored at the same time as the program in order to work.
Rotation of Media	Optimal rotation for backups include a scheme that supports generations of backups going back a number of months or even years, depending on legislative requirements or corporate risk. A typical, suggested rotation is: 2 weeks of daily backups that get rotated. This means there will be two Monday tapes which will get used each alternating week. These are the most critical tapes as files are usually lost or file corruption is noticed within this time period. There should be 2 months of Friday backups. This allows files that slip through the first crack to be caught relatively easily There should be a one or two years of 'month end' backups that occur on a specific 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 was the house counts in Theatre Manager, or recovery of an important graphic form last years brochure that should be used again) Ultimately, a good scheme may include 7 years of yearly backups to satisfy the Government should they come looking for data (don't forget that you need the program at the same time as the data). This represents 10+8+12+7 = 37 separate backup tapes or disks at a minimum. More will be required because repeated use of daily tapes will wear some out.
Offsite Storage	Backups need to be rotated off site - out of the office. Typically the most recent two daily backups are removed from the office the day after the backup is taken. This allows business recovery in the event of fire or theft from the principle site. Again, all data & programs on the same medium allows for faster recovery from the business outage.
Redundancy	In the case of physical backup device failure, it is important to own or have access to another similar device so that you can recover files or keep the backups proceeding until the primary device is repaired. Not everybody has this luxury and often take temporary measures.