World Backup Day is a day for everyone to learn about the increasing role of data in our lives and how important regular backups are. This day is not only a day for backing up data, but it is also a day to discuss the massive task of preserving the increasingly digital heritage and cultural works for future generations. Apparently, the previous year produced more than 2.5 quintillion bytes or 2.5 exabytes of data. Moreover, the past two years generated more data than the entire preceding recorded history. That is a significant amount of data to lose. Protect your data by backing up your data. World Backup Day is on March 31st. Then again, World Backup Day is every day since a quality backup plan is continuous.
Guidelines for Backup and Recovery of Databases
For organizations to run continuously, the ability to recover databases from robust backups is vital. Consequently, database administrators need to safeguard that backups and restores are valid. This text presents backup and recovery options for databases, including guidelines for assessing their effectiveness. For details, this text refers to Microsoft SQL Server.
Develop a Comprehensive Plan
A key responsibility of database administrators is to recover relational database management systems from the failure of media, hardware, and software. When such failures occur, the principal goal is to make sure that the database is available to users within an acceptable time interval without loss of data. Consequently, create and keep up a comprehensive plan for backup and recovery of databases. The plan includes all types of relational database management systems within the organization and covers several critical areas.
Decide What to Back Up
Be aware of the components of databases and related operating systems and applications that need backup and recovery. Perform complete backups initially. Execute backups incrementally after any updates, patches, upgrades, and configuration changes. Back up all operating system software of the database servers since a catastrophic event (such as a hardware failure) may need a complete recovery of the entire system, starting with the operating system. Back up all software for relational database management systems. If relevant, back up all software for applications using an operating system command. Preserve all administrative passwords that are part of the recovery process. Create backups of all system and user databases. Develop a separate maintenance plan for system databases. Set up all user databases with the full recovery model. Back up all database and transaction logs.
Determine the Type of Backup
For physical backups, set up all user databases with the full recovery model. Back up databases and transaction logs to be able to recover the database to the point of failure. Set up recovery models for the databases. Back up full, differential, and transaction logs. For very large databases with partitions among multiple files, use a strategy to back up files and filegroups. For logical backups, back up separate schema objects to flat files in any of the supported file formats, and test the recovery of the flat files.
Define a Strategy for Backups of Very Large Databases
To back up very large databases, partition the database among multiple files and use a strategy to back up files and filegroups. Write backups to multiple backup devices in parallel. Review the version and edition of the database to confirm availability of the parallel processing option.
Establish a Schedule for Backups
During backup, do not reduce available database server resources and slow down the activities of the database users. Consequently, select a backup window at a time when the lowest amount of activity affects the database. Establish different cycles of backups. Schedule full backups on the early morning of the first weekend day. Schedule incremental and differential backups throughout the weekdays. Schedule archiving and backups of transaction logs for every few hours, depending on the volatility of the database.
Decide Where To Store Backups
Back up to disk (that is, locally and via the network), transfer to tape and cloud, and store tapes off-site for disaster recovery. Compared to backups to tape and cloud, backups to disk are faster, and offer better control and monitoring. To shorten the mean time to recover, recover from backups on disk, which is faster than restoring from tape and cloud.
Develop a Policy for the Retention of Backups
The policy for how to keep backups relates to the rotation schedule of backup media (that is, disk, tape, and cloud). Decide upon the policy based on the service-level agreement established with the end-users. Ensure that the owner of the data specifies the retention period for the data. The retention time may vary from months to years, depending on local laws. Accordingly, delete old backups to create space for current backups. Carefully choose the policy for data retention, ensuring that the policy complements the policy for retaining backup media subsystems and the requirements for the strategy for the recovery of backups.
Manage Backups
After creating a robust backup plan and completing first work, manage backups properly. Archive without loss of time to tape and cloud immediately after backups to disk finish. View the status of backups. Review backup logs and backup catalog information periodically for any issues. Set up monitoring of backups using tools that include alerting and notifications to mobile devices for any failed backups. Rerun failed backup as soon as possible. Back up system databases, logs, and catalogs. Automate backups by scheduling backups. Validate and verify backups without performing an actual restore.
Test the Recovery of Backups
In addition to writing backups to media and storing backup media off site, periodically check the recovery from the backup media. Backups are not useful when is not possible to recover the data to the system at the time of need. Consequently, formulate a detailed strategy for the recovery task. That is, test restoring databases from backups stored on backup media (that is, disk, tape, and cloud). To confirm the recovery where possible, confirm and verify backups without performing an actual restore. Build periodically non-production databases from production backups using backup and recovery utility commands. Perform annual and bi-annual tests of the restore of databases from backups on tape and cloud as part of an audit. Explain the tests through a narrative with screen capture images. Preserve logs. Depending on the type of failure and the available backups, decide on whether to target a complete or partial recovery.
Establish Service-level Agreements
Draft a service-level agreement for backup and recovery. The agreement covers details of backup procedures. The agreement also includes a timeline for recovery. Ensure that management signs off on the agreement. The agreement does not aid in the recovery itself. Instead, the agreement sets the expectations of users and management for the recovery. As such, the agreement may offer more time to complete the recovery.
Develop a Plan for Disaster Recovery
Include databases as an important part of the distribution resource planning of the organization. Ensure that stakeholders understand the details of the recovery plan and in the order of restoration of the databases. Ascertain that the organization provides its response so that the most critical applications are available as soon as possible.
Collaborate with Colleagues
Enable colleagues to help with strengthening controls and processes for backup and recovery by validating the operations. The continuous and proactive cooperation with peers (such as internal auditors) assures management that the data of the organization is recoverable in a reliable and timely manner during disasters.
Stay Current on Tools
Keep up to date the knowledge and ability about tools for backup and recovery for relational database management systems, operating systems, and applications. During an actual disaster recovery event, there is no time to learn about any advancements in tools for backup and recovery.
Takeaways
Ascertain the continuity your organization by safeguarding that backups and restores of databases are valid. Plan your backup strategy, inventory all databases to avoid skipped orphans, prioritize critical data, centralize and automate how you manage backups, find and resolve backup failures, properly secure and store backups, plan quick access to critical data, automate the restore to recover quickly from disasters, test your backups before you need them, and be proactive.
For more details, including for Oracle databases, refer to texts such as “Database Backup and Recovery Best Practices” by ISACA Journal.
Achieve Better Backup and Recovery with IDERA
View your backup history with the free SQL Backup Status Reporter. Access more features with SQL Admin Toolset’s Backup Status. Level up to back up faster than native SQL Server, save space using dynamic compression, reduce failures from network problems, automate backups, ensure organizational compliance via policy-based management, back up to cloud storage, instantly access data from backups, receive alerts, and create reports with the full-featured SQL Safe Backup.
Also, discover instances on your network and check for versions with the free SQL Instance Check. Access more features with SQL Admin Toolset’s Discovery, Patch Analyzer, and Inventory Reporter. Level up to automatically discover new instances, find SQL Business Intelligence Services, identify databases on Always On Availability Groups, and organize using tags with the full-featured SQL Inventory Manager.