Managing Databases

This section provides comprehensive guidance on managing databases in SQL Server, covering essential tasks such as altering, dropping, and accessing database properties.

Important: Always ensure you have a recent backup before performing any significant database management operations.

Altering Databases

You can modify existing databases to change their properties, such as file growth, size, or compatibility level. The ALTER DATABASE statement is used for this purpose.

ALTER DATABASE AdventureWorks2019
SET COMPATIBILITY_LEVEL = 150;

ALTER DATABASE SalesDB
MODIFY FILE (
    NAME = SalesDB_Data,
    SIZE = 50MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 5MB
);

Dropping Databases

To remove a database that is no longer needed, you can use the DROP DATABASE statement. Ensure no active connections are using the database before dropping it.

USE master;
GO
DROP DATABASE OldSalesDB;
GO

Considerations Before Dropping

  • Verify there are no dependent objects or applications.
  • Ensure all necessary data has been backed up.
  • Check for active connections or transactions.

Database Properties

Each database has a set of properties that define its behavior and configuration. These can be viewed and modified using SQL Server Management Studio (SSMS) or T-SQL.

To view database properties using T-SQL:

SELECT
    name,
    database_id,
    create_date,
    state_desc,
    recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks2019';

Commonly Managed Properties:

  • Recovery Model: Controls how transactions are logged, affecting backup and restore operations (Simple, Full, Bulk-logged).
  • Collation: Defines the rules for sorting and comparing character data.
  • File Growth Settings: Determines how data and log files automatically increase in size.
  • Compatibility Level: Specifies the behavior of the database relative to a specific version of SQL Server.

Attaching and Detaching Databases

You can detach a database from one SQL Server instance and attach it to another. This is useful for migrating databases or performing maintenance.

-- Detaching a database
USE master;
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'MyDatabase', 'true';
GO

-- Attaching a database
USE master;
GO
EXEC sp_attach_db @dbname = 'MyDatabase',
    @filename1 = 'C:\SQLData\MyDatabase_Data.mdf',
    @filename2 = 'C:\SQLLogs\MyDatabase_Log.ldf';
GO

Alternatively, you can use SSMS for a graphical interface to attach and detach databases.

Database States

Databases can be in various states, such as ONLINE, OFFLINE, RESTORING, or SUSPECT. The state indicates the availability and operational status of the database.

You can change the state of a database:

-- Setting a database to OFFLINE
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

-- Setting a database to ONLINE
ALTER DATABASE MyDatabase SET ONLINE;
GO