Microsoft® SQL Server® 2012 Bible

554

Part V: Enterprise Data Management

operation is lost, and the restore must be made from the last transaction log. Therefore, if

the database uses the bulk-logged recovery model, every bulk-logged operation should be

immediately followed by a transaction-log backup.

This model is useful only when the database sees a large number of bulk-logged operations,

and if it’s important to increase their performance. If the database performs adequately dur-

ing bulk-logged operations in the full recovery model, bypass the bulk-logged recovery model.

A simple recovery model also minimally logs bulk-copy operations.

Using this setting is essentially the same as setting the Select Into/Bulkcopy data-

base option to true.

Best Practice

You should minimally use a bulk-logged recovery model because you lose the ability to do point-in-

time recovery to any point covered by a transaction log backup that contains even a single minimally

logged operation. The best prac tice for produc tion user databases is to use a full recover y model, take

a transaction log backup before performing bulk operations, switch to a bulk-logged model, perform

the bulk operations, and then immediately switch back to the full recovery model and take a transac-

tion log backup. This allows point-in-time recovery and fully protects the data.

Setting the Recovery Model

The model system database’s recovery model is applied to any newly created database. The

full recovery model is the default for the Standard and Enterprise Editions. The Personal

and Desktop editions use the simple recovery model as their default, but you can change

the default by setting the recovery model for the model system database.

Using Management Studio, you can easily set the recovery model on the Options tab of the

Database Properties dialog box. Select the database and right-click to get to the Database

Properties dialog.

In code, the recovery model is set with the ALTER DATABASE DDL command:

ALTER DATABASE DatabaseName SET Recovery Option;

The valid recovery options are FULL, BULK_LOGGED, and SIMPLE. The following code sets

the AdventureWorks2012 sample database to the full recovery model:

USE AdventureWorks2012;

ALTER DATABASE AdventureWorks2012 SET Recovery FULL;

You should explicitly set the recovery model in the code that creates the database.

c21.indd 554c21.indd 554 7/31/2012 9:22:47 AM7/31/2012 9:22:47 AM

http://www.it-ebooks.info