Review database configuration options
In this we will learn how to review database configuration options. Moreover, we will understand how to change database-level options in SQL Server 2019 (15.x) by using SQL Server Management Studio or Transact-SQL. These options are unique to each database and do not affect other databases.
Before You Begin
Limitations and Restrictions
Only those can modify these options who are:
- Firstly, the system administrator, database owner and members of the sysadmin
- Secondly, dbcreator fixed server roles and db_owner fixed database roles
Security
Permissions
For this, it requires ALTER permission on the database.
Using SQL Server Management Studio
To change the option settings for a database using SQL Server Management Studio:
- Firstly, in Object Explorer, connect to a Database Engine instance
- Secondly, expand the server and then, expand Databases.
- After that, right-click a database, and then click Properties.
- Lastly, in the Database Properties dialog box, click Options to access most of the configuration settings. However, file and filegroup configurations, mirroring and log shipping are on their respective pages.
Using Transact-SQL
To change the option settings for a database using Transact-SQL:
- Firstly, connect to the Database Engine.
- Secondly, from the Standard bar, click New Query.
- Lastly, copy and paste the following example into the query window and click Execute. This example sets the recovery model and data page verification options for the AdventureWorks2012 sample database.
SQL
USE master;
GO
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO
Reference: Microsoft Documentation