ALTER DATABASE in SQL Server - Complete Tutorial

Understanding ALTER DATABASE

ALTER DATABASE is a Transact-SQL command that lets you modify an existing database's structure and settings without deleting or recreating it.

Common uses include adding or removing data files, changing file sizes, setting database options like recovery model, and managing filegroups.

• Modify database files and filegroups
• Change database options (e.g., recovery model, compatibility level)
• Manage database states (e.g., set to single-user mode)
• Configure automatic behaviors (e.g., auto-shrink)

ALTER DATABASE Syntax Overview

The basic syntax of ALTER DATABASE varies depending on what you want to change. Here is a simplified form:

ALTER DATABASE database_name MODIFY FILE (NAME = logical_file_name, [options]);

• database_name: The name of the database to modify.
• logical_file_name: The logical name of the file to modify.
• Options include SIZE, MAXSIZE, FILEGROWTH, etc.

Modifying Database Files

You can add new files or modify existing files to manage storage and performance.

Adding files helps distribute data across multiple disks, improving I/O.

• Use ADD FILE to add data or log files.
• Use MODIFY FILE to resize files or change growth settings.
• Removing files requires the file to be empty and not the primary file.

Changing Database Options

ALTER DATABASE allows you to change various database options that affect behavior and recovery.

Common options include recovery model, compatibility level, and auto-shrink.

• SET RECOVERY changes the recovery model (FULL, SIMPLE, BULK_LOGGED).
• SET COMPATIBILITY_LEVEL adjusts the database compatibility with SQL Server versions.
• SET AUTO_SHRINK enables or disables automatic shrinking of database files.

Examples of ALTER DATABASE Commands

Here are practical examples demonstrating ALTER DATABASE usage.