INF: Recovery Planning for Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Databases (328747)

MORE INFORMATION

Backup Recovery Planning

Backup Recovery planning is an iterative process that involves the testing and the evaluation of different backup and restore solutions to meet an organization's requirements. With proper planning, you can recover from many failures, including:

• Media failure.
  
  
• User errors.
  
  
• Permanent loss of a server.

More information about topic follows.

Media Failure

Media failure constitutes one of the most common reasons for data loss, and hence the requirement to recover the databases from a backup. Media failure includes:

• Hard disk and disk controller errors, or both.
  
  
• RAID controller failures.
  
  
• Unexpected shutdowns and power outages that involve disk controllers with write-caching and no battery backup for the cache, or both.
  
  For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

  86903 INF: SQL Server and Caching Disk Controllers

  230785 INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage Algorithms Extend Data Reliability

User Errors

Unintentional or malicious deletes and updates, or both, from users and applications, or both, are another major reason for the undesired loss and modification of production data.

Permanent Loss of a Server

A natural disaster or a situation that calls for installing a new server requires you to have a complete database backup of all your databases.

You must identify the requirements for the availability of your data so you can choose the appropriate backup and restore strategy. Your overall backup strategy defines the type and frequency of backups and the nature and speed of the hardware required for them.

Important: Microsoft strongly recommends that you test your backup and recovery procedures thoroughly. Testing helps you to make sure that you have the required backups to recover from various failures, and that your procedures can execute smoothly and quickly if a real failure occurs.

How to Design a Backup Recovery Plan

The design of a Backup Recovery Plan involves the following steps:

• Analyze the Availability and Recovery Requirements
  
  To develop a successful backup and restore plan, you must understand when your data must be accessible and the potential affect of data loss on your business. Answering the questions in the "Analyzing Availability and Recovery Requirements" topic of SQL Server 2000 Books Online can help you determine your availability requirements and your sensitivity to data loss. You can then determine the correct recovery models for your databases, and make the necessary technical and financial tradeoffs.
  
  To download SQL Server 2000 Books Online, see the "References" section of this article.
• Planning for Disaster Recovery
  
  You must create a disaster recovery plan to make sure that all your systems and data can be quickly restored to normal operation if a natural disaster or a technical disaster occurs. When you create a disaster recovery plan, you prepare all the actions that must occur in response to a disaster. Microsoft recommends that you verify your disaster recovery plan through the simulation of a disaster.
  
  Consider disaster recovery planning for your environment and business needs. For example, suppose a fire occurs and wipes out your 24-hour data center. Are you certain you can recover?
  
  How long will it take you to recover and have your system available?
  
  How much data loss can your users tolerate?
  
  Ideally, your disaster recovery plan states how long recovery will take and the final database state the users can expect. For example, you might determine that after the acquisition of specified hardware, recovery will be completed in 48 hours, and that the data will be guaranteed only up to the end of the previous week. For more information, see the "Planning for Disaster Recovery" topic in SQL Server 2000 Books Online and the following Microsoft Knowledge Base article:

  307775 INF: Disaster Recovery Articles for Microsoft SQL Server

• How to Select a Recovery Model
  
  You can select one of three recovery models for each database in MSDE 2000 to determine how your data is backed up and what your exposure to data loss is. The three recovery models are:
  
  
  • Simple
  • Full
  • Bulk-Logged
  
  Each of these three models address different needs for:
  
  
  • Performance
  • Disk and tape space
  • Protection against data loss
  
  These are described in detail in the "Selecting a Recovery Model" topic in SQL Server 2000 Books Online. Depending on what operations you are performing, more than one model may be appropriate. After you select a recovery model, or models, plan the required backup and recovery procedures.

How to Verify the Integrity of Databases

Transact-SQL has a set of DBCC validation statements that you can use to verify the integrity of a database. For a list of DBCC validation statements, see the "Validation Statements" section of SQL Server Books Online topic "DBCC, Transact-SQL Reference." The DBCC validation statements perform internal consistency checks of the data and data pages in the database to make sure that a system or software problem has not damaged data.

The DBCC CHECKDB statement is one of the most frequently used validation statements. DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE statement were executed for each table in the database. Hence, if you have executed a DBCC CHECKDB statement recently, you do not have to run DBCC CHECKALLOC or DBCC CHECKTABLE.

To run data integrity tests for MSDE 2000 databases, you can execute DBCC statements by using the osql utility from the command line. The following example connects to the default MSDE 2000 instance by using Windows Authentication, executes DBCC CHECKDB on the pubs database, and then directs the result to C:\MSDE\DATA_INTEGRITY_REPORTS\dbcc_pubs.txt:

osql -E -Q "DBCC CHECKDB('pubs')" -o C:\MSDE\DATA_INTEGRITY_REPORTS\dbcc_pubs.txt

For additional information about the osql utility, click the article number below to view the article in the Microsoft Knowledge Base:

DBCC CHECKDB is a CPU- and disk-intensive operation. To provide a low overhead check of the physical consistency of the database, you can run the DBCC statement with the option PHYSICAL_ONLY:

osql -E -Q "DBCC CHECKDB('pubs') WITH PHYSICAL_ONLY" -o C:\MSDE\DATA_INTEGRITY_REPORTS\dbcc_pubs.txt

For more information about the PHYSICAL_ONLY option and other CHECKDB options, see the "DBCC CHECKDB, Transact-SQL Reference" topic in SQL Server Books Online.

After you run the command, it is very important to review the result of the DBCC command. For the example, open the Dbcc_pubs.txt in a text editor. Errors in the result indicate potential problems in the database that you must correct immediately. Leaving the database in this state can cause unrecoverable damage, and can leave your database in an unstable state.

If errors are detected, then you must consider restoring from a known good database backup as the primary means of fixing the errors. If you do not have a backup, the repair functionality of DBCC CHECKDB may be useful. However, using DBCC CHECKDB can be more time consuming than restoring from a backup, and you may not be able to recover all your data.

The repair levels of DBCC CHECKDB are:

• REPAIR_FAST
• REPAIR_REBUILD
• REPAIR_ALLOW_DATA_LOSS

Each level is described in detail in the "DBCC CHECKDB, Transact-SQL Reference" topic of SQL Server Books Online.

To run DBCC CHECKDB on a database with a repair level, you must explicitly set the database in single user mode. If the database is not in single user mode and you run DBCC CHECKDB with the repair level, you will receive the following error message: For additional information, click the article number below to view the article in the Microsoft Knowledge Base: For the following example assume that you ran DBCC CHECKDB on the pubs database, and that it reported errors and suggested REPAIR_REBUILD as the repair level. One of the ways to repair the pubs database is to connect to the MSDE 2000 instance, and then run the following Transact-SQL statements interactively:

C:\>osql -E

NOTE: This command connects you to the local, default instance of MSDE 2000 by using Windows Authentication. To connect to a named instance of MSDE 2000, the syntax is similar to:

C:\>osql -E -S servername\instancename

After you are connected, you get the osql prompt 1> where you can interactively enter Transact-SQL statements as shown:

1> use pubs 
2> go 
1> ALTER DATABASE testing SET SINGLE_USER 
2> go 
1> begin transaction -- NOTE: You are running the repair under a user transaction so that you can roll back the changes made by the repair command if the results are not satisfactory, such as too much data loss. 
2> DBCC CHECKDB('testing',REPAIR_REBUILD) 
3> go -- Verify the results. -- Verify the data in the corrupted objects. -- If the results are satisfactory, commit the user transaction by running the commands. 
1> commit transaction 
2> go -- If the results are not satisfactory, roll back the changes made by typing the command. 
1> rollback transaction 
2> go -- If the repairs are rolled back, the database still has errors and you must restore it from a good backup.
				

DBCC CHECKDB reports the results in the command window. Review the results, and verify if all the consistency errors are repaired. After the output of DBCC CHECKDB is clean, back up the database.

Caution: If running DBCC CHECKDB with one of the repair clauses does not correct the problem, or if you are not sure how this process may affect your data, contact your primary support provider.

DBCC CHECKDB RECOMMENDATIONS

DBCC CHECKDB is a CPU and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. Additionally, DBCC CHECKDB uses the tempdb database to perform sorting. If you actively perform transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log. Microsoft recommends that you run DBCC CHECKDB during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, you can also expect a performance hit on the transaction throughput and on the time it takes the DBCC CHECKDB statement to complete.

For more information, see the "DBCC CHECKDB Recommendations" topic in SQL Server 2000 Books Online.

You can use the Database Maintenance Plan Wizard to help you set up the core maintenance tasks you must have to make sure that your database:

• Performs well.
• Is regularly backed up if a system failure occurs.
• Is being regularly checked for any inconsistencies.

For more information, see the "Database Maintenance Plan Wizard" topic in SQL Server 2000 Books Online.

REFERENCES

To download an updated version of SQL Server 2000 Books Online, visit the following Microsoft Web site:

SQL Server 2000 Books Online (Updated)

This article contains a reference to a third party World Wide Web site. Third party sites are not under the control of Microsoft. Accordingly, Microsoft can make no representation about the content of these sites. Microsoft is providing this information only as a convenience to you.

Microsoft has not tested any software or information found on these sites, and therefore cannot make any representations about the quality, safety, or suitability of any software or information found there.

For additional information about Backup and Restore Strategies, visit the following DELL Web site:

Backup and Restore Strategies with SQL Server 2000