Tip: Change Settings with ALTER DATABASE in SQL Server 2008
Follow Our Daily Tips
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:
USE master; GO ALTER DATABASE CustomerSupport SET ENCRYPTION ON; GO
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:
USE master; GO ALTER DATABASE CustomerSupport SET READ_ONLY; GO
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.