Microsoft® SQL Server® 2012 Bible

576

Part V: Enterprise Data Management

The RECOVERY/NORECOVERY option is vital to the restore command. Every time a SQL

Server starts, it automatically checks the transaction log, rolling back any uncommitted

transactions and completing any committed transactions. This process is called recovery,

and it’s a part of the ACID properties of the database.

Therefore, if the restore has the NORECOVERY option, SQL Server restores the log with-

out handling any transactions. Conversely, RECOVERY instructs SQL Server to handle the

transactions. In the sequence of the recovery operation, all the restores must have the

NORECOVERY option enabled, except for the last restore, which must have the RECOVERY

option enabled.

Deciding between RECOVERY and NORECOVERY is one of the complications involved in try-

ing to write a script to handle any possible future recovery operation.

The STANDBY option enables the recovery effects to be undone.

If the recovery operation includes a transaction-log restore, the recovery can stop before

the end of the transaction log. The options STOPAT and STOPATMARK leave the end of

the transaction log unrestored. The STOPAT accepts a time, and the STOPATMARK restores

only to a transaction that was created with a named mark. The STOPBEFOREMARK option

restores everything up to the beginning of the marked transaction.

The REPLACE option creates the database and its related fi les even if another database

already exists with the same name.

Chapter 36, “Creating Triggers,” details SQL Server transactions and how to create marked

transactions.

The following script demonstrates an example of a restore sequence that includes a full

backup and two transaction-log backups:

-- BackUp and recovery example

CREATE DATABASE Plan2Recover;

Result:

Command(s) completed successfully.

Continuing:

USE Plan2Recover;

CREATE TABLE T1 (

PK INT Identity PRIMARY KEY,

Name VARCHAR(15)

);

c21.indd 576c21.indd 576 7/31/2012 9:22:53 AM7/31/2012 9:22:53 AM

http://www.it-ebooks.info