524
Part V: Enterprise Data Management
You may encounter a situation in which the database is inaccessible and you do not have a
backup. To access the database no matter what state things are, members of the sysadmin
role can put the database in EMERGENCY mode. When in EMERGENCY mode, the database
is in read-only mode and is accessible only by members of the sysadmin role. To put the
AdventureWorks2012 sample database in EMERGENCY mode in code, execute the following
command:ALTER DATABASE AdventureWorks2012 SET EMERGENCY;
The READ_ONLY database-state settings enable only selects from the database. READ_ONLY
cannot take effect if any users are in the database. To reset the database to a normal read-
and-write state, the READ_WRITE database setting is used.To set the AdventureWorks2012 sample database to a READ_ONLY state in code, do the
following:ALTER DATABASE AdventureWorks2012 SET READ_ONLY;
The restricted access database-state settings are also available. The three restricted access
levels are single_user, restricted_user, and multi_user states. These settings con-
trol which users are allowed to access the database. The SINGLE_USER setting is appropri-
ate when you do database maintenance. The RESTRICTED_USER setting enables database
access only to users in the db_owner, dbcreator, and sysadmin roles. The MULTI_USER
setting is used to set the database in the normal operating state.To set the AdventureWorks2012 sample database to SINGLE_USER access in code, do the
following:ALTER DATABASE AdventureWorks2012 SET SINGLE_USER;
To revert this change and set the AdventureWorks2012 database access to MULTI_USER
access in code, do the following:ALTER DATABASE AdventureWorks2012 SET MULTI_USER;Compatibility Level
In SQL Server, the database-compatibility level can be set to 90 (SQL Server 2005), 100 (SQL
Server 2011) or 110 (SQL Server 2011). When a database is upgraded to SQL Server 2012 from
any earlier version of SQL Server, the database retains its existing compatibility level. If
the original compatibility level of the database is 80 (SQL Server 2000) or earlier, then the
database automatically is set to the server’s lowest compatibility mode, which is 90 (SQL
Server 2005). Setting the database-compatibility level to a level lower than 110 may be
necessary if you upgrade the Database Engine and still need to maintain the behavior of an
earlier version of SQL Server.The compatibility level option does not provide full backward compatibility. It is mainly intended to enable new
reserved words to be used in tables, and retain some (limited) changed behavior. Refer to SQL Server Books Online
for a full overview.c19.indd 524c19.indd 524 7/30/2012 5:43:13 PM7/30/2012 5:43:13 PM
http://www.it-ebooks.info