TechNet Magazine > Home > Tips > SQL Server >  Change Settings with ALTER DATABASE in SQL Serv...
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:

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.








Page view tracker