Изменение настроек базы данных

Изменение настроек базы данных производится с помощью команды ALTER DATABASE.

Данная команда позволяет добавлять или удалять файлы и файловые группы из базы данных, а также изменять параметры базы данных или ее файлов и файловых групп.

ALTER DATABASE database_name { MODIFY NAME = new_database_name | COLLATE collation_name | <add_or_modify_files> | <add_or_modify_filegroups>} <add_or_modify_files>::={ ADD FILE <filespec> [ ,...n ] [ TO FILEGROUP { filegroup_name } ] | ADD LOG FILE <filespec> [ ,...n ] | REMOVE FILE logical_file_name | MODIFY FILE <filespec>} <filespec>::= ( NAME = logical_file_name [ , NEWNAME = new_logical_name ] [ , FILENAME = {'os_file_name' | 'filestream_path' } ] [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] ) <add_or_modify_filegroups>::={ ADD FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> |DEFAULT | NAME = new_filegroup_name }} <filegroup_updatability_option>::={ { READONLY | READWRITE } | { READ_ONLY | READ_WRITE }}

database_name - имя изменяемой базы данных.

MODIFY NAME = new_database_name - присваивает базе данных новое имя, указанное в аргументе new_database_name.

COLLATE collation_name - определяет параметры сортировки для базы данных. Аргумент collation_name может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Если аргумент не указан, базе данных будут назначены параметры сортировки экземпляра SQL Server.

< add_or_modify_files >::=

ADD FILE

Добавляет файл к базе данных.

TO FILEGROUP { filegroup_name }

Указывает файловую группу, к которой необходимо добавить указанный файл. Чтобы отобразить текущую файловую группу и узнать, какая файловая группа в данный момент установлена по умолчанию, используйте представление каталога sys.filegroups.

ADD LOG FILE

Добавляет файл журнала в указанную базу данных.

REMOVE FILE logical_file_name

Удаляет логическое описание файла из экземпляра SQL Server и физический файл. Файл не может быть удален, если он не пуст. logical_file_name - логическое имя, используемое в SQL Server при обращении к файлу.

MODIFY FILE

Указывает файл, который должен быть изменен. Одновременно может быть изменено только одно свойство файла. Предложение NAME всегда должно быть указано, чтобы определить, какой файл будет изменен. Если указано предложение SIZE, новый размер файла должен быть больше, чем текущий.

Чтобы изменить логическое имя файла данных или файла журнала, указывается логическое имя файла, который будет переименован, в предложении NAME и новое логическое имя для файла в предложении NEWNAME.

Например:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

Чтобы переместить файл данных или файл журнала в новое расположение, указывается текущее логическое имя файла в предложении NAME и новый путь и имя файла в операционной системе в предложении FILENAME.

Например:

MODIFY FILE

( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

<add_or_modify_filegroups>::=

ADD FILEGROUP filegroup_name

Добавляет файловую группу в базу данных.

REMOVE FILEGROUP filegroup_name

Удаляет файловую группу из базы данных. Файловая группа не может быть удалена, пока она не пустая. Вначале удалите из файловой группы все файлы.

MODIFY FILEGROUP filegroup_name{ <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name }

Изменяет файловую группу, меняя ее состояние на READ_ONLY или READ_WRITE, делая ее файловой группой по умолчанию для базы данных или изменяя имя файловой группы.

DEFAULT

Изменяет файловую группу по умолчанию базы данных на аргумент filegroup_name.

NAME = new_filegroup_name

Изменяет имя файловой группы на аргумент new_filegroup_name.

<filegroup_updatability_option> - устанавливает свойство «только для чтения» или «чтение и запись» для файловой группы.

· READ_ONLY | READONLY

Определяет, что файловая группа находится в состоянии только для чтения. Изменение ее объектов запрещено. Первичную файловую группу перевести в состояние только для чтения нельзя. Чтобы изменить это состояние, необходимо обладать монопольным доступом к базе данных.

· READ_WRITE | READWRITE

Определяет, что файловая группа находится в состоянии READ_WRITE. Разрешено изменять объекты в файловой группе. Чтобы изменить это состояние, необходимо обладать монопольным доступом к базе данных. Ключевое слово READWRITE будет удалено в будущей версии Microsoft SQL Server. Вместо него следует используовать READ_WRITE.

<set_database_options >::=

Одна из опций базы данных, приведенных выше, или их комбинация.

Примеры изменения настроек базы данных:

Пример 1:

В следующем примере к базе данных TestDatabase добавляется файл данных размером 5 МБ.

ALTER DATABASE TestDatabase

ADD FILE

(

NAME = NewDataFile,

FILENAME = 'C:\MSSQL\DATA\NewDataFile.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

Пример 2:

В следующем примере в базе данных TestDatabase создается файловая группа NewUserFG и добавляется два файла по 5 МБ в эту файловую группу.

ALTER DATABASE TestDatabase

ADD FILEGROUP NewUserFG;

GO

ALTER DATABASE TestDatabase

ADD FILE

(

NAME = NewDataFile2,

FILENAME = 'C:\MSSQL\DATA\NewDataFile2.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

),

(

NAME = NewDataFile3,

FILENAME = 'C:\MSSQL\DATA\NewDataFile3.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP newUserFG;

Пример 3:

В следующем примере к базе данных TestDatabase добавляется файл журнала NewLogFile размером 5 МБ.

ALTER DATABASE TestDatabase

ADD LOG FILE

(

NAME = NewLogFile,

FILENAME = 'C:\MSSQL\DATA\NewLogFile.ldf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

);

Пример 4:

В следующем примере из базы данных TestDatabase удаляется один из ранее добавленных файлов с логическим именем NewDataFile3. Прежде чем файл может быть удален, он должен быть совершенно пуст. Для переноса данных из одного файла данных в другие файлы той же файловой группы используется инструкция DBCC SHRINKFILE с указанием предложения EMPTYFILE.

-- перемещает данные из файла NewDataFile3

-- в другие файлы файловой группы newUserFG

DBCC SHRINKFILE (NewDataFile3, EMPTYFILE);

GO

-- удаляет пустой файл

ALTER DATABASE TestDatabase

REMOVE FILE NewDataFile3;

Удаление базы данных

Удаление базы данных осуществляется командой DROP DATABASE.

DROP DATABASE database_name

При удалении базы данных она удаляется из экземпляра SQL Server, а ее физические файлы удаляются с диска. Если база данных или один из ее файлов во время удаления находится в автономном режиме, файлы с диска не удаляются, эти файлы можно удалить вручную при помощи обозревателя Windows.

Удалить базу данных, которая используется в текущий момент времени, невозможно. Для отключения пользователей от базы данных можно использовать инструкцию ALTER DATABASE для перевода базы данных в режим SINGLE_USER.

База данных может быть удалена независимо от ее состояния: в автономном режиме, доступна только для чтения, подозрительная и т. д. Для просмотра текущего состояния базы данных можно воспользоваться представлением каталога sys.databases.

Удаленная база данных может быть повторно создана только с помощью восстановления из резервной копии.

Пример:

В следующем примере удаляется ранее созданная база данных TestDatabase.

DROP DATABASE TestDatabase ;

Лекция № 12