SQL ALTER DATABASE is an essential statement to modify the properties of an existing database in a Relational Database Management System (RDBMS). This statement is helpful if you want to rename a database, change its composition, or modify file properties.
Basic Syntax
The basic syntax for the ALTER DATABASE statement is as follows:
Basic Syntax:
ALTER DATABASE database_name [MODIFY <option> ...]Here, database_name specifies the database you wish to alter, and <option> is a keyword indicating the type of modification.
Common Options in ALTER DATABASE
The following are some of the most common options that can be used with the ALTER DATABASE statement:
Renaming a Database
One of the most common uses of the ALTER DATABASE statement is to change the name of a database. The MODIFY NAME clause lets you easily change the database name. For example, to rename a database from "old_db" to "new_db":
ALTER DATABASE old_db MODIFY NAME = new_db;Modify Database Files
To change the properties of a database file, use the MODIFY FILE option:
MODIFY FILE (name = 'file_name', size = <size>, FILE_ACCESS = <access>);
The name specifies the name of the file, the size sets the size of the file in bytes, and the FILE_ACCESS determines the access mode for the file (READ_ONLY, READ_WRITE, or WRITE_ONLY). For example, to resize a file named "old_file" to 10MB, use the following statement:
ALTER DATABASE database_name MODIFY FILE (name = 'old_file', size = 10);
Control User Access
Another common use for the ALTER DATABASE statement is setting a database to single-user or restricted-user mode. You can accomplish this by using the SET clause with the SINGLE_USER or RESTRICTED_USER option. Setting a database to single-user mode limits access to one user at a time, while restricted-user mode allows only members of db_owner or sysadmin roles. For example, to set a database named "mydb" to single-user mode, use the following statement:
ALTER DATABASE mydb SET SINGLE_USER;
To set a database named "mydb" to restricted-user mode, use the following statement:
ALTER DATABASE mydb SET RESTRICTED_USER;
Change Database Collation
Using the COLLATE clause, you can also change the default collation of a database with the ALTER DATABASE statement. Collation sets the rules for sorting and comparing characters. For example, to change the collation of a database named "mydb" to "utf8mb3_unicode_ci", use the following statement:
ALTER DATABASE mydb COLLATE utf8mb3_unicode_ci;
Modify Recovery Model
You can change the recovery model of a database with the ALTER DATABASE statement. The recovery model sets the database's strategy for recovering data in the event of a failure. The most commonly used recovery models are "FULL" and "SIMPLE". The full recovery model maintains a complete set of transaction logs, providing the most comprehensive data recovery. In contrast, the simple recovery model keeps only the most recent transaction logs, providing less comprehensive data recovery. To change the recovery model of a database named "mydb" to "Simple," use the following statement:
ALTER DATABASE mydb SET RECOVERY SIMPLE;
Conclusion
The ALTER DATABASE statement is a powerful tool for modifying the properties of an existing database. By understanding its basic syntax and some of the most common options, you are well-equipped to manage your databases effectively.