建库
格式说明
CREATE DATABASE 数据库名
[ON]
(
[NAME = 逻辑文件名,]
FILENAME = '完整路径物理文件名.mdf' //第一个文件的后缀为.mdf
[,SIZE = 文件初始大小]
[,MAXSIZE = {文件最大容量|UNLIMITED}] //unlimited 不限制
[,FILEGROWTH = 递增值] //注意最后一个指令没有逗号
)[,…n] //如果主文件组多个文件,注意逗号间隔
[,FILEGROUP 文件组名]
(
[NAME = 逻辑文件名,]
FILENAME = '完整路径物理文件名.ndf' //除了第一个文件其他文件后缀都是.ndf
[,SIZE = 文件初始大小]
[,MAXSIZE = {文件最大容量|UNLIMITED}]
[,FILEGROWTH = 递增值]
)[,…n]
]
[LOG ON
(
[NAME = 逻辑文件名,]
FILENAME = '物理文件名.ldf' //日志文件的后缀为.ldf
[,SIZE = 文件初始大小]
[,MAXSIZE = {文件最大容量|UNLIMITED}] //unlimited 不限制
[,FILEGROWTH = 递增值] //注意最后一个指令没有逗号
)[,…n]
]
建库的同时建两个文件组,分别为SalesGroup1、SalesGroup2.
CREATE DATABASE Sales
ON
(
NAME = Spri1_dat,
FILENAME = 'F:\17yuan\Spri1_dat.mdf',
SIZE = 10,
FILEGROWTH = 5,
MAXSIZE = 50
),
(
NAME = Spri2_dat,
FILENAME = 'F:\17yuan\Spri2_dat.ndf',
SIZE = 10,
FILEGROWTH = 5,
MAXSIZE = UNLIMITED
),
FILEGROUP SalesGroup1
(
NAME = SGrp1Fi1,
FILENAME = 'F:\17yuan\Sgrp1Fi1.ndf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5
),
FILEGROUP SalesGroup2
(
NAME = SGrp2Fi1,
FILENAME = 'F:\17yuan\SGrp2Fi1.ndf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
(
NAME = Sales_log,
FILENAME = 'F:\17yuan\Sales_log.ldf',
SIZE = 3,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
修改数据库
添加文件组
alter database test
add filegroup TestGroup
GO
--向TestGroup组添加文件
alter database test
add file
(
name = test_data,
filename = 'F:\test\test_data.ndf',
size = 5,
maxsize = 50,
filegrowth = 5
) to filegroup TestGroup
增加日志文件
alter database test
add log file
(
name = test_log,
filename = 'F:\test\test_log.ldf',
size = 3,
maxsize = 50,
filegrowth = 10%
)
修改数据文件
alter database test
modify file
(
name = test_data,
size = 6,
filegrowth = 6,
maxsize = unlimited
)
修改数据库名称
第一种方法
alter database test
modify name = test_2
第二种方法
exec sp_renamedb test_2,test
修改文件组名
alter database test
modify filegroup TestGroup
name = TestGroup_2
删除文件
alter database test
remove file test_data
删除文件组 注!如果文件组有文件,则不能删除
alter database test
remove filegroup TestGroup_2
删除数据库
drop database test