Microsoft® SQL Server® 2012 Bible

523

Chapter 19: Confi guring SQL Server

19

TABLE 19-12 Database-State-Confi guration Properties

Property Level* Graphic Control Code Option

Database OffLine D Management Studio ALTER DATABASE <DB Name> SET

OFFLINE

Database OnLine D Management Studio ALTER DATABASE <DB Name> SET

ONLINE

EMERGENCY D - ALTER DATABASE <DB Name> SET

EMERGENCY

Read-Only D Management Studio ALTER DATABASE <DB Name> SET

READ_ONLY

Restricted

Access — Members

of db_owner,

dbcreator, or

sysadmin

D Management Studio ALTER DATABASE <DB Name> SET

RESTRICTED_USER

Restricted

Access — Single

user

D Management Studio ALTER DATABASE <DB Name> SET

SINGLE_USER

Multi User D Management Studio ALTER DATABASE <DB Name> SET

MULTI_USER

Compatibility Level D Management Studio ALTER DATABASE <DB NAME> SET

COMPATIBILITY_LEVEL

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

You can set the state of the database with the ALTER DATABASE command. The

sp_dboption command has been removed in this version of SQL Server and can no longer

be used.

Database-Access Level

The database-access-confi guration options set the state of the database. When the database

is offl ine, no access to the database is enabled.

To set the AdventureWorks2012 sample database to an OFFLINE state in code, do the

following:

ALTER DATABASE AdventureWorks2012 SET OFFLINE;

To revert this change and make the AdventureWorks2012 database online and available in

code, execute the following command:

ALTER DATABASE AdventureWorks2012 SET ONLINE;

c19.indd 523c19.indd 523 7/30/2012 5:43:13 PM7/30/2012 5:43:13 PM

http://www.it-ebooks.info