As with any database system, there is always a need to have a backup and recovery strategy in place. This strategy will depend on the database system and hardware platform involved in the individual enterprise architecture. Every system is different, and every company is different. Design the backup schedule that best fits your needs and comply with your IT policies.
A backup and recovery plan should include the following items:
Documenting the current configuration
Creating a fault-tolerant environment
Preparing for immediate recovery
Documenting the database recovery plan
Below are some suggested techniques to consider when designing your strategy for database backup and recovery.
To completely back up your system, you should back up all databases in the system and their transaction logs. Don't forget to back up the master and msdb databases as well. Without good backups, you might not be able to restore your data in the event of failure.
Perform a complete (full) database backup. A complete database backup consists of all the file groups and data files that are part of the database being backed up. This will allow you to fully recover from a failure if one were to occur. If your database is too big to perform a full backup, you should consider a differential backup along with a transaction log backup strategy.
If a complete backup is used, it is recommended to dump the transaction log and shrink the log file after a successful backup occurs. The log file(s) will continue to grow and can take up excess physical space if not dumped/shrunk on a regular basis. These can be scheduled to occur as part of your scheduled backup strategy.
Store backups off-site. If you store your backups off-site, the backups might survive a disaster such as fire or flood. The backup data is much more important than the computer system itself.
Verify the backup. A backup might not always be successful. Tapes can go bad, especially tapes that are frequently used. By verifying the backup, you will at least know that the tape is good.
Rotate the backup tapes so that you can restore at least several days' worth of information, if necessary.
Keep records of how the backup works and how to rebuild the system if necessary. Remember that you may not always be there to rebuild the system yourself.
Remember that the type of backup performed drives the nature of the restore operation that will need to occur. An important feature of the restore operation is the RECOVERY option. The RECOVERY option instructs SQL Server to attempt to recover the database by using the online transaction log after the restore is complete. If you're planning on using a differential and/or transaction log backup files, be sure you specify the NORECOVERY option.
One of the most important decisions that an Oracle DBA has to make is to decide whether to run the database in ARCHIVELOG mode or not. The archive log files contain the changes made to the database. There are advantages and disadvantages to running the database in ARCHIVELOG mode.
Backups can be categorized into physical and logical backups. A physical backup is a backup where the actual physical database files are copied from one location to another (usually from disk to tape). Examples include: operating system backups, backups using Recovery Manager, cold backups, and hot backups. Logical backups are backups that extract the data using SQL from the database and store it in a binary file. This data can be imported back into the same database or a different database at a later time. The Export/Import utility provided by Oracle can be used to take logical backups of a database.
A backup of an Oracle database includes the data files, control files, and the archived redo log files.
You should not back up online redo log files. There
is no danger in backing up online log files as long as you don't accidentally
apply them during recovery. During a media failure, a DBA is under pressure
to bring up the database as soon as possible. If you restore your database
from backup, there is a possibility that you could overwrite the existing
online log files with the backed
If you are using OS-specific scripts to automate backups, in order to avoid unnecessary maintenance, do not make the scripts dependant on the object names in the database. To accomplish this, use SQL queries against the database dictionary to dynamically generate backup scripts.
Tag each backup with a timestamp for proper identification during recovery, and to track the progress through logging. Timestamps for each step in the backup script should be logged into a backup script log file, which can be used by the administrator to very that a backup procedure ran successfully.