Full Redundancy Backup Strategy

A database management system requires a special strategy, and this is one that is effective, particularly in small to medium sized shops where there is not always the ability to separate the computer operations duties from those of the database analysts.

This strategy, with some modification can also be used for user files such as those on a network file server.

Backups require that data be backed up and also that the consistency and content of the backup also be monitored. For example, if a programmer accidentally truncates a table, it doesn’t do any good to be backing up that table on a regular basis if there’s no data contained within it.

The ideal situation for this backup strategy is that the organization should also have a backup server that is a complete duplicate of the production server.

Performing a disk-to-disk backup before the scheduled full tape backup is a second level of disaster prevention which allows a tape backup failure and still provides the means to completely recover a hard drive in the event of a crash.

A backup server (disk-to-disk) can be expensive, but also serves a number of additional purposes:

• Can be used as a reporting server so that reports are not being run against the production database can causing performance problems.

• Provides for a complete test environment for testing software upgrades, unit testing for conversions, testing data loads, data entry testing and all other aspects of testing.

• Provides a fail over solution if the main server goes down.

• Can also be used as a web server for any report deployment.

Routine and Process for a Daily Backup

Note that these backup routines could be varied where an organization is unable to shut down their production server, or in the cases where time windows may be too small for a full database export, backup, copy and restore.

This is an ideal situation for complete redundancy.

1. Run database consistency checks on the production server.
2. The production database tables are exported individually to disk on the production server.
3. The DBMS vendor supplied database backup routine is run on the production server.
4. The DBMS instance on the production server is shut down.
5. The cold backup of all the data, the DBMS instance and the copied out individual tables is done onto tape on the production server.
6. The DBMS instance on the production server is brought back online.
7. The individual exported production database tables are copied to the backup server using a file operating system command.
8. The tables on the backup server are truncated.
9. The tables on the backup server are then loaded from the individually exported backup tables on the backup server.
10. Run database consistency checks on the backup server.
11. The DBMS vendor supplied database backup routine is run on the backup server.
12. The DBMS instance on the backup server is shut down.
13. The cold backup of the backup server is done.
14. The DBMS instance on the backup server is brought back online.

This strategy has the advantage of providing complete redundancy on dual servers so that fail over in the case of one going down is easily done.

Is also has the advantage of checking the consistency of the data since an individual table that has become corrupt will not load into the backup server.

Also, if the analysts are using the backup server for reporting purposes, any inconsistencies, missing data or corrupt data is likely to be discovered more quickly since reporting will tend to make this visible very quickly.

The export of the individual tables can be done using batch files that also log the number of rows exported in a text file. This allows the operator to review these files in the morning so that the number of rows exported can be checked and verified in case there are any anomalies. Because the information is in text files, it also makes review using a dial-up connection much easier.