Tip: Change Settings with ALTER DATABASE in SQL Server 2008

Follow Our Daily Tips

Twitter | Blog | RSS | Facebook

For SQL Server 2008 and later releases, the ALTER DATABASE statement replaces the sp_dboption stored procedure as the preferred way to change database settings. To change database settings, you must be a member of a role granted the ALTER permission on the database or be explicitly assigned this permission. When you execute an ALTER DATABASE statement, a checkpoint occurs in the database for which the option was changed, and this causes the change to take effect immediately. Most of the options listed accept a value of ON or OFF, which is used to set the state of the option. For example, you can enable transparent data encryption on the CustomerSupport database by using the following command:

T-SQL

USE master; 
GO 
ALTER DATABASE CustomerSupport 
SET ENCRYPTION ON; 
GO

PowerShell

Invoke-Sqlcmd -Query "USE master; ALTER DATABASE CustomerSupport 
SET ENCRYPTION ON;" -ServerInstance "DataServer91\CorpServices"

Some options explicitly set a specific state. For example, if no users are currently connected to the CustomerSupport database, you could set the database to read-only by using the following command:

T-SQL

USE master; 
GO 
ALTER DATABASE CustomerSupport 
SET READ_ONLY; 
GO

PowerShell

Invoke-Sqlcmd -Query "USE master; ALTER DATABASE CustomerSupport 
SET READ_ONLY;" -ServerInstance "DataServer91\CorpServices"

From the Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant, Second Edition by William R. Stanek.

Looking for More Tips?

For more tips on using Microsoft products and technologies, visit the TechNet Magazine Tips library.