MS SQL Basics Tutorial: Changing the database

6.4.1 change files and their properties

You can use database property settings in Enterprise Manager to change the database file and transaction log files, or you can change the database with the ALTER DB command. The ALTER Database command can add or remove files from the database, or modify the properties of the file. It should be noted that only the database administrator (DBA) or database owner with CREATE DATABASE permissions is authorized to execute this command. Administration

The syntax for the ALTER DATABASE command is as follows:

The parameters are described as follows:

ADD FILE

To FILEGROUP

Specifies which filegroup to add files to. ADD LOG FILE

Specify the transaction log files to increase REMOVE file

Deletes the definition of the specified file from the database system table and deletes its physical file. The file can be deleted only if it is empty.?? ADD FILEGROUPB

Specify the filegroup to be added REMOVE FILEGROUP

Deletes the definition of the specified filegroup from the database and deletes any database files it includes. The filegroup can be deleted only if it is empty. MODIFY FILE

Modifies attributes such as file name, capacity size, maximum capacity, and file increase mode for the specified files. However, only one property of one file can be modified at a time. When using this option, it should be noted that in file format filespec You must explicitly specify the name of the file with name: If the file size is already determined, the new size must be larger than the current file capacity; FileName can only specify files that exist in the tempdb database , and the new file name does not work until SQL Server restarts. MODIFY FILEGROUP filegroup_name Filegroup_property

Modify Filegroup Properties Filegroup_property Filegroup_property The preferred value is:

READONLY: Specifies that the filegroup is read-only primary filegroup primary file group cannot be specified as read-only. Only users with exclusive access to the database can flag a filegroup as read-only.