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"