Ali Navid Akhtar, OCP, Jeff Buchholtz, Michael Ryan, CIA, CPA, and Kumar Setty, CISA
The ability to restore databases from valid backups is a vital part of ensuring business continuity. Backup integrity and restorations are an important piece of the IT Governance Institute’s IT Control Objectives for Sarbanes-Oxley, 2nd Edition. In many instances, IT auditors merely confirm whether backups are being performed either to disk or to tape, without considering the integrity or viability of the backup media.
This article covers the topics related to data loss and the types of database backup and recovery available. Best practices that can assist an auditor in assessing the effectiveness of database backup and recovery are also provided. This article focuses on the technologies and capabilities of the Oracle relational database management system (RDBMS) and Microsoft (MS) SQL Server because, together, they cover approximately 40 percent of all database installations. Figure 1 provides a short comparison of Oracle and MS SQL Server.
One of the key responsibilities of a database administrator (DBA) is to prepare for the possibility of media, hardware and software failure as well as to recover databases during a disaster. Should any of these failures occur, the major objective is to ensure that the database is available to users within an acceptable time period, while ensuring that there is no loss of data. DBAs should evaluate their preparedness to respond effectively to such situations by answering the following questions:
The following is a checklist for database backup and recovery procedures that are explained throughout this article:
DBAs are responsible for making a comprehensive backup plan for databases for which they are accountable. The backup plan should include all types of RDBMSs within the enterprise and should cover the following areas:
After making a solid backup plan and completing initial work, the DBA should properly manage backups, keeping the following points in mind:
Set up a process so that disk backups get transferred to tape without loss of time.
Imagine the following scenario: A flood has hit the area in which a company’s headquarters resides, and the entire IT infrastructure has been damaged, but not destroyed. Before the event, the DBAs performed backups to the backup media, following all of the processes noted previously in this article, and had these stored offsite. In the enterprise’s most recent IT audit, the auditor rated the backup process as “effective.”
The backup media from the offsite storage is retrieved and loaded. A message appears on-screen that states that the backup media are “unreadable” due to integrity issues. What could have happened?
Many things could have happened. However, it is clear that a critical step did not happen. The restoration from the backup media was never really tested. The control was marked as effective because a backup process was in place and being performed. In addition, no errors were ever received when the enterprise backed up to the backup media.
Backups are of no use if the IT team cannot restore the data to the system at the time of need. A DBA should formulate a detailed strategy for this task:
The DBA team must draft a backup and recovery SLA, covering details of backup procedures and including a timeline for recovery, and have management sign off on it. The SLA does not assist in the recovery process itself, but sets the user community’s (and management’s) expectations for the recovery process, which may provide the team more time to complete the restore process.
The DBA should take care to ensure that databases are included as a key element in the company’s overall DRP. All stakeholders need to understand the elements of the recovery plan and in what order the IT team will restore the databases. The business must provide its input at this stage so that the most business-critical applications are available as soon as possible.
It seems obvious, but DBAs play the final and most important role in the process in that they must keep their knowledge of backup and recovery tools for RDBMSs up to date. During the actual restore event, DBAs will not have time to figure out any advancements in backup and recovery tools.
The primary responsibility of the database administration team is to review all types of RDBMSs in the enterprise and to develop a comprehensive backup plan to conduct effective backup management by proactively monitoring backups, getting alerted for failed backups and rerunning these seamlessly, without loss of time. It is good practice to back up data to physical disk and to then archive the data to tape for disaster recovery purposes.
Once an approach has been established, it is imperative to test data restoration periodically as part of the backup and restore strategy, and to review all options before executing the actual restoration/recovery. It is important to confirm that the DBA team is abreast of the latest backup and recovery tools and to ensure that the team has a clearly documented process in place with clear responsibilities. If DBAs maintain proper backups, monitor these proactively and can provide assurance of the recovery of data up to the point required by the business, they have done a major part of the job for which they were hired.
IT auditors can assist data administration teams in strengthening their controls and data recovery processes by validating the DBA operations, including the testing of the recovery of data. This continuous, proactive and cooperative effort between internal audit and the DBA team can provide assurance to management that, in the event of a disaster, the business’s data can be recovered.
Ali Navid Akhtar, OCP, has more than two decades of experience with databases. He works as a lead database administrator at Solo Cup Co.
Jeff Buchholtz, has more than 18 years of design, implementation and support of global IT technology solutions. He works in an IT leadership role and is an Oracle database administrator.
Michael Ryan, CIA, CPA, is the director of internal audit for Solo Cup Co., with the primary responsibility of building and executing US Sarbanes-Oxley Act 404 compliance strategies.
Kumar Setty, CISA, has more than 10 years of experience in the areas of data analysis, systems administration, auditing and computer security. He is a manager at PricewaterhouseCoopers LLP.
Enjoying this article? To read the most current ISACA Journal articles, become a member or subscribe to the Journal.
The ISACA Journal is published by ISACA. Membership in the association, a voluntary organization serving IT governance professionals, entitles one to receive an annual subscription to the ISACA Journal.
Opinions expressed in the ISACA Journal represent the views of the authors and advertisers. They may differ from policies and official statements of ISACA and/or the IT Governance Institute and their committees, and from opinions endorsed by authors’ employers, or the editors of this Journal. ISACA Journal does not attest to the originality of authors’ content.
© 2012 ISACA. All rights reserved.
Instructors are permitted to photocopy isolated articles for noncommercial classroom use without fee. For other copying, reprint or republication, permission must be obtained in writing from the association. Where necessary, permission is granted by the copyright owners for those registered with the Copyright Clearance Center (CCC), 27 Congress St., Salem, MA 01970, to photocopy articles owned by ISACA, for a flat fee of US $2.50 per article plus 25¢ per page. Send payment to the CCC stating the ISSN (1526-7407), date, volume, and first and last page number of each article. Copying for other than personal use or internal reference, or of articles or columns not owned by the association without express permission of the association or the copyright owner is expressly prohibited.