ALTER DATABASE: Essential Commands at a Glance

Recovery Models: The Foundation

SIMPLE Recovery

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;

What it does: Truncates transaction log after every checkpoint. No point-in-time recovery.

Aspect	Details
Log space usage	Very low (auto-cleared)
Backup frequency	Full backups only (no log backups)
Recovery precision	Last backup only (can lose committed work)
AlwaysOn support	NO—cannot be in availability group
Best for	Dev/test databases, data warehouses (ETL-only)

FULL Recovery

ALTER DATABASE MyDatabase SET RECOVERY FULL;

What it does: Preserves all transaction log records. Enables point-in-time recovery to any moment (within log backup retention).

Aspect	Details
Log space usage	High—requires regular log backups or log grows unbounded
Backup strategy	Full backup + frequent log backups (hourly, 15-min, etc.)
Recovery precision	Point-in-time to the exact second
AlwaysOn support	YES—required for production availability groups
Best for	Production OLTP, mission-critical data

⚠ Gotcha: If you set a database to FULL recovery but never back up the transaction log, it will grow until it consumes all disk space. Always schedule log backups before switching to FULL.

BULK_LOGGED Recovery

ALTER DATABASE MyDatabase SET RECOVERY BULK_LOGGED;

What it does: Hybrid mode. Minimally logs bulk operations (INSERT...SELECT, BULK INSERT, index rebuild) but fully logs normal transactions.

Aspect	Details
Log space usage	Medium—bulk ops reduce logging overhead
Performance during bulk ops	Faster (less logging = fewer I/O waits)
Recovery precision	Log backups only (cannot recover to middle of bulk op)
AlwaysOn support	YES—but not recommended for production
Best for	Temporary mode during large ETL jobs or index rebuilds

Typical workflow:

ALTER DATABASE SET RECOVERY BULK_LOGGED
Run your bulk operations (INSERT...SELECT, BULK INSERT, etc.)
ALTER DATABASE SET RECOVERY FULL
Back up transaction log immediately

File and Filegroup Management

Add a Data File

ALTER DATABASE MyDatabase
ADD FILE (
  NAME = 'MyDatabase_Data2',
  FILENAME = 'D:\SQLData\MyDatabase_Data2.mdf',
  SIZE = 1GB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 256MB
)
TO FILEGROUP [PRIMARY];

Use this when the primary filegroup is running out of space. The database automatically distributes new data across all files in a filegroup (proportional fill algorithm).

Add a New Filegroup

-- Create filegroup
ALTER DATABASE MyDatabase
ADD FILEGROUP FG_2024;

-- Add file to the filegroup
ALTER DATABASE MyDatabase
ADD FILE (
  NAME = 'MyDatabase_FG2024',
  FILENAME = 'E:\SQLData\MyDatabase_FG2024.mdf',
  SIZE = 2GB
)
TO FILEGROUP FG_2024;

Use filegroups for table partitioning, separation of concerns (fact vs. dimension tables), or performance optimization (spread I/O across spindles).

Remove a File or Filegroup

-- First, empty the filegroup (move data out)
ALTER DATABASE MyDatabase
REMOVE FILE MyDatabase_FG2024;

-- Then, remove the filegroup itself
ALTER DATABASE MyDatabase
REMOVE FILEGROUP FG_2024;

⚠ Order matters: Remove the file first, then the filegroup. If data still exists on that file, removal fails.

Database State Options

Take Database Offline

ALTER DATABASE MyDatabase SET OFFLINE;

Use cases:

Performing maintenance without users connected
Detaching a database to move files
Forcing all users to disconnect (useful before ALTER DB... MODIFY FILE)

Safe disconnect: Use OFFLINE with ROLLBACK IMMEDIATE to force-close all sessions:
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;

Bring Database Online

ALTER DATABASE MyDatabase SET ONLINE;

Makes the database accessible to users again. If recovery is needed, SQL Server runs it automatically.

Set Database to READ_ONLY

ALTER DATABASE MyDatabase SET READ_ONLY;

Use cases:

Historical/archive databases (prevent accidental writes)
DR standby databases (ensure no changes occur locally)
Compliance (immutable snapshots)

Set Database to READ_WRITE

ALTER DATABASE MyDatabase SET READ_WRITE;

Restores write capability. Undo for READ_ONLY.

Compatibility Level

Compatibility level controls SQL Server feature set and execution engine behavior. Always match your SQL Server version for new databases.

-- Set to SQL Server 2022 (150)
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 150;

-- Set to SQL Server 2019 (150)
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 150;

-- Set to SQL Server 2017 (140)
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 140;

Version	Compatibility Level
SQL Server 2022	160
SQL Server 2019	150
SQL Server 2017	140
SQL Server 2016	130
SQL Server 2014	120

When to upgrade compatibility level:

After a SQL Server version upgrade (after thorough testing)
To enable new query optimizer features (e.g., adaptive joins in SQL 2017+)
As part of gradual migration to a newer SQL Server version

Log and Snapshot Options

Modify Log File Size

ALTER DATABASE MyDatabase
MODIFY FILE (
  NAME = 'MyDatabase_log',
  SIZE = 5GB,
  MAXSIZE = 20GB,
  FILEGROWTH = 512MB
);

Use this to pre-allocate log space (reduces auto-grow contention) or cap log growth to prevent runaway disk consumption.

Create a Database Snapshot

CREATE DATABASE MyDatabase_Snapshot_20240101
  AS SNAPSHOT OF MyDatabase;

Use cases:

Read-only point-in-time view of the database
Before large operations (test rollback scenarios)
Ad-hoc reporting without impacting production queries

⚠ Cost: Snapshots share pages with the source database. The snapshot file grows as the source is modified (copy-on-write). Very expensive for large, busy databases.

Collation and Encryption

Change Database Collation

ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CI_AS;

Impact: Changes collation for all columns without explicit collation. Requires offline (all users disconnected). Test thoroughly.

⚠ Risky: Changing collation mid-production can break queries, indexes, and constraints. Only do this if absolutely necessary, with extensive testing.

Enable Transparent Data Encryption (TDE)

-- Create Database Master Key (one-time)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

-- Create Certificate (one-time)
CREATE CERTIFICATE MyDatabaseCertificate
  WITH SUBJECT = 'Database Encryption Certificate';

-- Enable TDE
CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE MyDatabaseCertificate;

ALTER DATABASE MyDatabase
SET ENCRYPTION ON;

TDE encrypts data at rest (files, backups). Use for compliance (HIPAA, PCI-DSS). Minimal performance overhead on modern hardware.

Critical Database Options

AUTO_SHRINK

-- DISABLE IT (default is OFF, keep it that way)
ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

Why: Auto-shrink runs shrink operations automatically during idle periods, fragmenting indexes and wasting CPU. Let DBAs control shrinking manually if needed.

AUTO_CLOSE

-- DISABLE IT
ALTER DATABASE MyDatabase SET AUTO_CLOSE OFF;

Why: Auto-close closes the database after the last user disconnects, releasing memory. Then the next connection must reopen it (expensive). Kills connection pooling efficiency.

AUTO_UPDATE_STATISTICS

-- ENABLE IT (usually default)
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS ON;

SQL Server updates statistics automatically when the optimizer detects they're stale. Critical for query performance.

RESTRICT_ACCESS

-- Allow only members of db_owner role to connect
ALTER DATABASE MyDatabase SET RESTRICT_ACCESS = DB_OWNER;

-- Restore full access
ALTER DATABASE MyDatabase SET RESTRICT_ACCESS = MULTI_USER;

Use cases: Maintenance windows, troubleshooting, preventing accidental use during testing.

Common ALTER DATABASE Patterns

Production Database Checklist

-- Recovery: FULL (for point-in-time)
ALTER DATABASE Production SET RECOVERY FULL;

-- Statistics: Auto-update enabled
ALTER DATABASE Production SET AUTO_UPDATE_STATISTICS ON;

-- Auto-shrink: Disabled (manual control only)
ALTER DATABASE Production SET AUTO_SHRINK OFF;

-- Auto-close: Disabled (keep connection pool alive)
ALTER DATABASE Production SET AUTO_CLOSE OFF;

-- Compatibility: Match your SQL Server version
ALTER DATABASE Production SET COMPATIBILITY_LEVEL = 150;

-- Collation: Verify standard for your org
-- (Usually SQL_Latin1_General_CI_AS_KS_WS for US/EU environments)

-- Check current settings
SELECT
  name,
  recovery_model_desc,
  is_read_only,
  is_auto_shrink_on,
  is_auto_close_on,
  compatibility_level
FROM sys.databases
WHERE name = 'Production';

Before Large Maintenance Operations

-- Switch to BULK_LOGGED for speed
ALTER DATABASE Production SET RECOVERY BULK_LOGGED;

-- Perform index rebuild / statistics update / bulk insert
-- ... your operation here ...

-- Switch back to FULL
ALTER DATABASE Production SET RECOVERY FULL;

-- Take log backup immediately
BACKUP LOG Production TO DISK = 'D:\Backups\Production_Log_AfterMaintenance.trn';

Common Pitfalls

Pitfall 1: Setting RECOVERY FULL without scheduling log backups → disk fills up → database stops accepting writes → production outage.

Pitfall 2: Using ALTER DATABASE ... MODIFY FILE to reduce file size when it's already in use → error, requires OFFLINE mode first.

Pitfall 3: Changing COMPATIBILITY_LEVEL without testing application queries → subtle behavior changes break production workflows.

Pitfall 4: ALTER DATABASE SET OFFLINE without ROLLBACK IMMEDIATE → hangs indefinitely if users are connected.

The Bottom Line

ALTER DATABASE is your control panel for database behavior. Recovery models, file layout, and operational state all flow through it. Master these commands, and you'll avoid 80% of production database disasters.

Remember: test in non-prod first. ALTER DATABASE changes can be disruptive (offline mode, compatibility shifts, recovery mode changes). Understand the impact before executing in production.