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