Microsoft® SQL Server® 2012 Bible

525

Chapter 19: Confi guring SQL Server

19

To set the compatibility level of the AdventureWorks2012 sample database to 110 (SQL

Server 2012) in code, do the following:

ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 110;

To view the compatibility level of SQL Server, query the compatibility_level column in the sys.databasescatalog

view.

Recovery-Confi guration Properties

The recovery-confi guration properties, as shown in Table 19-13, are used to set recovery

options in SQL Server.

TABLE 19-13 Recovery-Confi guration Properties

Property Level* Graphic Control Code Option

Recovery Model D Management Studio ALTER DATABASE <DB Name> SET

RECOVERY

PAGE VERIFY D Management Studio ALTER DATABASE <DB Name> SET

PAGE_VERIFY

Media Retention S Management Studio EXEC sp_configure 'media

retention'

Backup Compression S Management Studio EXEC sp_configure 'backup

compression default'

Recovery Interval S Management Studio EXEC sp_configure 'recovery

interval'

* The confi guration level refers to server, database, or connection.

The recovery options determine how SQL Server handles transactions and the transaction

log, and how the transaction log is backed up.

Recovery Model

SQL Server 2012 uses a recovery model to confi gure several settings that work together to

control how the transaction log behaves regarding fi le growth and recovery possibilities.

The three recovery model options are as follows:

■ (^) Simple: The transaction log contains only transactions that are not yet written to
the data fi le. This option does not provide up-to-the-minute recovery.
c19.indd 525c19.indd 525 7/30/2012 5:43:13 PM7/30/2012 5:43:13 PM
http://www.it-ebooks.info