Modifying databases (Changes to existing DB)

Modifying a database is not a very common occurrence, if you have created your database to allocate disk space dynamically, you may never need to alter it. Nonetheless, there may be occasions when it is necessary to modify a database. In such cases, you use the ALTER DATABASE Transact-SQL statement:

Here's how you can create an ALTER DATABASE Transact-SQL statement in SQL Server 2022:
Example 1: Changing the Database Collation
If you want to change the collation of an existing database, you can use:

ALTER DATABASE [DatabaseName]
COLLATE Latin1_General_CI_AS;

This statement changes the collation of DatabaseName to Latin1_General_CI_AS.
Example 2: Modifying Database Size Options
If you need to adjust the size settings for the database, such as autogrowth or initial size:

ALTER DATABASE [DatabaseName]
MODIFY FILE (
    NAME = N'DatabaseName',
    SIZE = 10MB,
    FILEGROWTH = 5MB
);

This statement modifies the primary data file of DatabaseName to have an initial size of 10MB and to grow by 5MB each time more space is needed.
Example 3: Adding a New File To add a new file to the database:

ALTER DATABASE [DatabaseName]
ADD FILE (
    NAME = N'DatabaseName_Data2',
    FILENAME = N'C:\SQLData\DatabaseName_Data2.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
);

This adds a new secondary data file to DatabaseName with the specified parameters.
Example 4: Changing Recovery Model
To change the recovery model of the database:

ALTER DATABASE [DatabaseName]
SET RECOVERY FULL;

This sets the recovery model to FULL, which means all database operations are fully logged for potential recovery or point-in-time restore. Notes:

• Replace [DatabaseName] with the actual name of your database.
• Be cautious when altering database settings as these changes can impact performance, backup strategies, and data integrity
• Ensure you have the necessary permissions to execute these commands. Typically, you need at least ALTER permissions on the database.
• Always backup your database before making significant changes.

Remember, the effectiveness of these commands can vary based on your specific database setup, current usage, and the environment of SQL Server. It's wise to test changes in a non-production environment first.

For example, suppose you want to INCREASE the size of the transaction log assigned to database employee to four megabytes from one megabyte. If you used the default values when creating the database, the database will be stored in a file named "employee.mdf" and the transaction log will be stored in a file named "employee_Log.ldf." To INCREASE the size of the transaction log, you'd issue the following statement:

ALTER DATABASE employee 
MODIFY FILE (NAME = "employee_Log", SIZE = 4MB)

Note that when you use the Name parameter, you do not include the file extension .ldf. Also, the MB designation for megabytes is optional. Note: It is important to note that you cannot modify a database to a smaller size, only to a larger size. In the next lesson, we will cover the simple task of deleting a database. It is time to begin the course project! Click the Exercise link below to write Transact-SQL statements that create and modify the database that we will build throughout the remainder of the course.