Following the recommendation of a daily full database dump, with hourly transaction log dumps, ensures that your system can always be recovered to the state of the last transaction log dump and that data loss is minimized. The following strategy is recommended:
- Database Backup—Daily Backup: Daily from live database to hard drive (database dumps). Daily tape backups of database dumps are recommended.
- Dumping Transaction Logs.
- Hourly: Append to existing log.
Nightly: Overwrite the existing log (use the INIT option if using T-SQL to define backup operation).
Note Failure to run transaction log dumps during the day means that a hard crash during the day may result in the loss of ALL data since the last full backup.
Backup Strategy for the System Database
Aptify recommends that the system databases (Master, MSDB and Model) be backed up at the same time as the APTIFY Database.
Backup Tape Rotation Recommendations
Aptify recommends following a system using 4 generations of backup tapes: Daughter A, Mother B, Grandmother C, Great-Grandmother D.
The sample schedule below shows a pattern to be repeated through the year.:
Week | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
1 | A | A | A | A | B | C | - |
2 | A | A | A | A | B | - | - |
3 | A | A | A | A | B | - | - |
4 | A | A | A | A | B | - | - |
5 | A | A | A | A | B | C | - |
9 | A | A | A | A | B | C | - |
52 | A | A | A | A | B | C | D |
Level D of the recommended backup schedule should be done once yearly.
To ensure tape quality for daily backups, replacing tapes on a periodic basis is recommended. The specifications for the tape media in use should state the recommended interval for tape replacement.
It is very important to validate tape backups on a periodic basis. These tapes are to be used to restore the full system to another server. If the full restore does not function properly in a test environment, the tapes cannot be counted on to provide full recovery from a catastrophic event. Aptify recommends a full tape verification on a periodic basis. The most conservative approach is to test each tape backup. However, most organizations do not want to devote the time needed to test the backup media each day. It is usually considered conservative to test the tape media each week by restoring the data to a test server.
Off-Site Storage of Backup Files
The greater the diversity of storage location for backup files, the less the risk of data loss.
Aptify recommends that all backup files(daily and otherwise) be kept off-site. Either automatically copy them to geographically diverse areas of our corporate network, or better yet, employ a secure Cloud backup service. Although inconvenient, the seriousness of data loss makes this step worthwhile.
Validating Backup Tapes When Updating the Test Server
If the Test Server is restored from the Production backup tape on a periodic basis, the integrity of the backup tapes may be monitored.
| Note |
|---|
Before restoring data from the Production to the Test Server, be sure to back up any ongoing work on the Test Server and save it to another location to avoid the loss of test configurations or data. |