HP Application Software Suite for Microsoft SQL specs

8 Recovery for Microsoft SQL Server

Microsoft SQL Server Recovery Models

Microsoft SQL Server offers three disaster-recovery models:

•Simple Recovery Model - The Simple Recovery model enables you to restore a database to the point at which a virtual copy or backup was created; it does not enable you to restore a database to a specific point in time. Therefore, any data modifications made between the time of the most recent backup and the time of the failure are lost. This recovery model uses circular logging to minimize the amount of space used for transaction logs.

•Full Recovery Model - The Full Recovery model enables you to restore a database to a specific point in time; either to a virtual copy created before an error occurred or to the point just before which a system failure occurred. Under this recovery model, transaction logs are saved until a failure occurs, and therefore no data is lost.

•Bulk-Logged Recovery Model - The Bulk-Logged Recovery model is used for high-performance operations such as bulk copying and bulk data modification.

NOTE: If you are recovering data from SQL servers across multiple domains, ensure you have stored the login and password for each domain on your backup server. For more information, see “Configuring Recovery Manager Policies” (page 20).

Recovering Data with HP 3PAR Recovery Manager

HP 3PAR Recovery Manager for Microsoft SQL Server supports both recovery from virtual copy and recovery from media. However, only recovery from virtual copy enables you to perform a point-in-time or point-of-failure recovery.

Therefore, if you are running SQL Server under the Full Recovery model, you must perform a restore from a virtual copy.

Preparing for the Recovery Process

Before recovery begins, you must ensure the database can be taken offline.

1.Check whether database ID 9 is full.

When the SQL server takes a database offline, it writes a checkpoint record to database ID 9. After this log file becomes full, the SQL server cannot write the checkpoint record and therefore cannot take the database offline.

2.If database ID 9 is full, either:

• Back up the transaction log for the database

• Detach the database

Recovery Manager uses the Volume Shadow Copy Service (VSS) framework to restore data. Because the VSS framework takes a database offline before it allows you to restore it, you do not need to place the database in single-user mode.

Recovering from Logical Errors with Point-in-Time Recovery

Point-in-time recovery is useful for recovering from logical errors. For example, if you accidentally altered an entry or deleted a table, you can use a virtual copy to restore your instance or database back to a consistent, known prior state. Point-in-time recovery restores the instance or database to the state it was in at the time the virtual copy was created.

To perform a point-in-time recovery, use Recovery Manager to perform a file copy restore or a volume restore. The restore steps depend on whether your SQL servers are in a clustered or non-clustered environment.

68 Recovery for Microsoft SQL Server