At the core of any Disaster Recovery Plan is the recovery model that has been envisaged. Microsoft SQL Server 2005 provides the DBA with three kinds of recovery models—full, simple and bulk-logged. The recovery model determines the basic behavior of the back up and restores operations that will be put in place for the database. This in turn depends on the availability and recovery requirements of the organization.
Managing Backup Media
Backup plans must include methodologies for managing backup media. Our DBAs design a tracking and management plan for recycling backup sets, a schedule for overwriting backup media, decide on whether to use centralized or distributed backups, set up the means of tracking the useful life of media, define a procedure to minimize the effects of loss of backup sets and take decisions to store backup sets offsite and the impact on recovery time.
Running a Base-Functionality Script
A base functionality script is used as part of the disaster recovery plan to ensure that everything is working as intended. It is application specific and can take different formats. It may be a .sql script file or a .bat file containing osql or bcp commands or both. It provides a tool for the system administrator or database administrator to verify whether the database has been restored to its viable state without end user intervention.
Ensuring Disaster Readiness
Ensuring Disaster Readiness is part of our DBAs Disaster Recovery plan. The backup and recovery procedures have to be thoroughly tested, defined and documented for smooth execution. Regular database and transaction log backups have to be performed to minimize the amount of lost data. It is advisable to maintain system logs in a secure fashion and keep a list of service packs installed on Microsoft Windows or SQL Server. Network libraries have to be recorded and security modes and sa passwords have to be stored. A simulation of the recovery steps should be done on a separate server and the base functionality script has to be maintained to ensure minimum capability assessment.