Backup – Databases

Database – Backups

Databases when backing up is some cases require a commercial backup program. Databases unlike common Documents and Spreadsheets are files that open and running. Basically a database needs/should be stopped to backup effectively. Several backup programs will attempt to copy a Database and fail because of this. Always make sure you backup method or program can backup the database in question effectively.

When a database is backed up incorrectly, or Backed up but the support software (example: commercially purchased accounting, billing, point of sale, CRM, and business directed) is not maintained with the provider, the database is less likely to be recovered and accessible.

Comprehensive Backup Plan

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:

  • Decide what needs to be backed up. It is imperative that the DBA be aware of database and related OS and application components that need to be backed up, whether via an online backup or an offline cold backup.
    The following are details of what needs to be backed up:

    • OS software—An event such as a hardware failure will require a complete system restore, starting with the OS, so there is a need to back up the database server OS initially and after any system updates or configuration changes.
    • RDBMS software—The RDBMS software should be backed up initially and after any patches/upgrades.
    • Application software where applicable— This applies especially to Oracle E-Business Suite, Oracle Application Server and Oracle Enterprise Manager (OEM). The application DBA should complete an initial full backup of the applications to disk using an appropriate OS command and, then, schedule future incremental backups, e.g., after any patches/ upgrades. These backups should also be transferred to tape.
    • Passwords—All superuser passwords that may be required during recovery should be preserved. It is a good idea to ensure that the default passwords that came with the initial installation of the RDBMS are changed.
    • All components of Oracle databases:
      • Database parameter file—A parameter file or server parameter file (SPFILE) defines persistent initialization parameters of a database, including information about database control files.
      • Database control file(s)—The control file stores the status of physical structure of the database. If it becomes unavailable, the database cannot operate. It is imperative that these files be backed up while backing up other components of the database. In later versions of Oracle (9i onward), the DBA can configure automatic backup of the parameter file as well as the control file to ensure that these get backed up after each backup and after any structural changes in the database.
      • Database data files—These should be backed up during cold backup as well as during online backup, using Oracle’s Recovery Manager (RMAN) or, in Oracle Database versions in which RMAN was not introduced, by putting tablespaces in backup mode. The DBA should try to run all production databases in Archive log mode so that recovery to the point of failure is possible.
      • Redo log files and archived redo logs—While making a cold backup, the DBA needs to backup redo logs. When the database is running in archive log mode and doing and online backup, the DBA needs to archive redo logs manually or automatically and then back up all archive redo logs.
      • Oracle network files—It is important to back up all Oracle network files initially and after any change.
      • Password files—Password files when used should be backed up initially and after any change.
    • MS SQL Server databases:
      • Back up both system and user databases.
      • Have a separate maintenance plan for system databases, i.e., master, model, msdb. Master supports only full backups; tempdb backup is not required, as it gets rebuilt during SQL Server startup.
      • Back up all user databases. Set up all user databases for full recovery model, and back up both database and transaction logs.
  • Determine the appropriate backup type to use for your data.
    • Oracle databases:
      1. Logical backups—This type of backup is performed through Oracle utilities “exp.” From version 10g onward, Data Pump can also be used. The whole database, individual schemas, tables or tablespaces can be backed up. Restore is done using “imp” or Data Pump. With such backups, recovery to the point of failure is not possible.
      2. Physical offline or cold backups—The database must be shut down and a copy must be made of all essential data files and other components of the database.
      3. Physical online or hot backups—This method enables the database to be backed up while the database is up and running. The following points should be kept in mind while doing online backups:
        • Either put the tablespaces in backup mode and back up the associated data files using an OS copy command, or use RMAN, a robust tool provided by Oracle for backup and recovery with version 8.x onward. Oracle adds new functionality to this tool with each version. RMAN can use the database control file to keep its catalog, or the DBA can setup schema for each database, in a separate database for RMAN catalogs.
        • The DBA must review and keep in mind the RMAN compatibility matrix for the database being backed up/ restored as well as the RMAN executable and RMAN Catalog Database/Schema.
        • DBAs must familiarize themselves with full, incremental and differential backups and set these up using RMAN scripts. DBAs must review their RDBMS edition, e.g., incremental backups are not possible in standard editions prior to Oracle 10g. To restore/ recover a database to the point of failure or a previous point in time, the DBA must put the database in archive log mode and back up all archived redo logs.
        • It is important not to forget to back up the RMAN catalog at the end of each backup. DBAs can do an export backup of RMAN catalog schema.
    • SQL Server databases:
      1. Logical backups—In SQL Server, individual schema objects can be backed up to flat files in any of the supported file formats. Then flat files can be restored using tools such as the bcp utility, the Import and Export Wizard, or the SQL Server Integration Services tools.
      2. Physical backups—It is recommended that all user databases be set up for full recovery model, and both database and transaction logs should be backed up to restore/recover the database to the point of failure. DBAs should thoroughly familiarize themselves with database recovery models and full, differential and transaction-log backups, and set these up accordingly. File or filegroup backup strategy can be used if the databases to be backed up are very large databases (VLDBs) that are partitioned among multiple files.
  • Establish a strategy for handling VLDB backups—In Oracle, the DBA can reduce the backup window for VLDBs by allocating multiple channels and fine-tuning backups, can save disk space by using compressed backups, and can block tracking with incremental backup techniques with the latest versions. The DBA must review the version and edition of the database to confirm availability of this option. If this does not do the trick, the DBA can consider setting up split mirror backups. For SQL Server, the DBA can partition the database among multiple files and use the file or filegroup backup strategy. Also, using multiple backup devices in SQL Server allows backups to be written to all devices in parallel.
  • Establish an appropriate backup schedule and window— It is good practice to select a backup window at a point when the lowest amount of activity affects the database so that the backup does not reduce available database server resources and slow down the database user’s activity. The DBA can tune the backup window by parallelizing backups using multiple channels; however, the DBA must review the version and edition of the database to confirm availability of this option. In the vast majority of cases, it is best to set up a weekly backup cycle starting with full backups on Friday night or Saturday morning and incremental/differential backups throughout the weekdays. Archive/transaction log backups can be scheduled for every few hours, depending on the volatility of the database.
  • Decide where to store backups—Both Oracle and MS SQL Server databases can be backed up directly to tape or disk (locally or over the network), and then the backups can be archived to tape. It is good practice to back up to disk, transfer to tape and store tapes offsite for disaster recovery (DR). The backups to disk are faster; DBAs have more control and can better monitor these and, with this method, DBAs hold two sets of backups—one on disk, the other on tape. During restore, if backups are still on disk, it will be a faster restore, reducing mean time to recover (MTTR).
  • Develop a backup retention policy—The backup retention policy relates to both the disk and tape rotation schedule and should be decided upon based on the SLA established with the business-user community. The data owner should specify the retention period for the data. The retention period may vary from months to years, depending on local laws. Accordingly, the DBA should be deleting old backups to create space for current backups. The data retention policy should be chosen carefully, making sure that it complements the backup media subsystem retention policy and requirements for the backup recovery strategy. If not using a catalog, the DBA must ensure that the control file record keep time instance parameter matches the retention policy.

News