Tutorials SQL Server 2005In this tutorial you will learn about Disaster Recovery in SQL Server 2005, Recovery Models, Managing Backup Media, Running a Base-Functionality Script and Ensuring Disaster Readiness.
Most enterprises have mission critical data which is the very backbone of the business. The loss of such data would be disastrous to the business and may even result in closure. In such circumstances it is not surprising that organizations are very concerned about Disaster Recovery procedures. SQL Server 2005 provisions for just such a scenario by helping the DBA create a Disaster recovery plan and put in place recovery steps that can be tested for efficiency long before the disaster actually strikes.
Disaster Recovery plans hover around concepts such as time to recover. The Time to recover concept predicts the time required for an organization to put its database back into an active state, after the disaster strikes. This could range from 48 hours to a week. The structuring of the plan would also require some thought. It could include plans to acquire hardware, communication plans, and list of contacts, instructions to disaster recovery personnel and information to plan administrators and so on.
SQL Server 2005 supports clustering, backup and restore and log shipping as means of disaster recovery. It adds a number of enhancements to the features available in the earlier versions.
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.
Backup plans must include methodologies for managing backup media. DBAs must 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.
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 is part of the 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.
| How to take backup & recovery of database in SQL Server 2005. Explain each & every step. |
