ALTER DATABASE in SQL Server

This article will go over various ALTER DATABASE statements in SQL Server. This aids in changing the database name, properties, files, recovery methods, file locations, and so on.

Changing the state of a database or file group to READ ONLY or READ WRITE necessitates exclusive database access. To obtain exclusive access, the following example switches the database to SINGLE USER mode. The example then changes the database’s state to READ ONLY and grants all users access to the database.

USE master;
GO
ALTER DATABASE dbStudents
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbStudents
SET READ_ONLY
GO
ALTER DATABASE dbStudents
SET MULTI_USER;
GO

The following is an example of changing the name of a database in SQL server using the ALTER DATABASE statement.

USE master;
GO
ALTER DATABASE dbStudents
MODIFY NAME = dbStudentsInfo;
GO

The following is an example to change the .mdf/.ldf filenames.

USE master;
GO
ALTER DATABASE dbStudents
MODIFY FILE ( NAME = Students_Log, NEWNAME = dbStudents_Log);
GO

The following is an example to change the .mdf/.ldf file location

USE master;
GO
ALTER DATABASE dbStudents;
MODIFY FILE ( NAME = dbStudents_Log, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\New_File\dbStudents_Log.ldf’ );
GO

The snapshot isolation framework option for the database is enabled in the following example.

USE [database_name];
USE master;
GO
ALTER DATABASE [database_name]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

The following example enables database change tracking and sets the retention period to two days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

The following example shows how to change the retention period to 3 days.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

The following example shows how to disable change tracking for the AdventureWorks2012 database.

ALTER DATABASE [database_name]
SET CHANGE_TRACKING = OFF;

The following example enables the Query Store and configures its parameters.

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1024,
INTERVAL_LENGTH_MINUTES = 60
);