数据库操作
1.创建单一数据库
CREATE
DATABASE
test
ON
(
NAME = main_dat, -- 数据库名称
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/maindat.mdf ' , -- 物理路径
SIZE = 10 , -- 初始容量,默认单位是MB
MAXSIZE = 50 , -- 最大容量
FILEGROWTH = 5 -- 增长值
)
LOG ON
(
NAME = ' main_log ' ,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/maindat.ldf ' ,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
ON
(
NAME = main_dat, -- 数据库名称
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/maindat.mdf ' , -- 物理路径
SIZE = 10 , -- 初始容量,默认单位是MB
MAXSIZE = 50 , -- 最大容量
FILEGROWTH = 5 -- 增长值
)
LOG ON
(
NAME = ' main_log ' ,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/maindat.ldf ' ,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
2.创建带文件组的多文件数据库
CREATE
DATABASE
company
ON
PRIMARY -- 主数据文件组,可以直接用PRIMARY
(
NAME = Pri1_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ Pri1dat.mdf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 15 % -- 也可以用百分比来作为增长率
), -- 在一个组中有多个文件时,用","分隔开
(
NAME = Pri2_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ Pri2dat.ndf ' , -- 一个数据库只能有1个mdf
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 15 %
),
FILEGROUP ComGroup1
(
NAME = Grp1File1_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G1Fi1dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
),
(
NAME = Grp1File2_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G1Fi2dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
),
FILEGROUP ComGroup2
(
NAME = Grp2File1_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G2Fi1dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
),
(
NAME = Grp2File2_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G2Fi2dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
)
LOG ON
(
NAME = ' Company_log ' ,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ comlog.ldf ' ,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
GO
ON
PRIMARY -- 主数据文件组,可以直接用PRIMARY
(
NAME = Pri1_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ Pri1dat.mdf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 15 % -- 也可以用百分比来作为增长率
), -- 在一个组中有多个文件时,用","分隔开
(
NAME = Pri2_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ Pri2dat.ndf ' , -- 一个数据库只能有1个mdf
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 15 %
),
FILEGROUP ComGroup1
(
NAME = Grp1File1_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G1Fi1dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
),
(
NAME = Grp1File2_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G1Fi2dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
),
FILEGROUP ComGroup2
(
NAME = Grp2File1_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G2Fi1dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
),
(
NAME = Grp2File2_dat,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ G2Fi2dt.ndf ' ,
SIZE = 10 ,
MAXSIZE = 50 ,
FILEGROWTH = 5
)
LOG ON
(
NAME = ' Company_log ' ,
FILENAME = ' e:/program files/Microsoft sql server/mssql/data/ comlog.ldf ' ,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
GO
3.查看数据库信息
使用存储过程sp_helpdb可以查看当前服务器上所有数据库的信息,如果指定了数据库的名称,则返回指定数据库信息
sp_helpdb
sp_helpdb pubs
sp_helpdb pubs
使用sp_databases可以查看当前服务器上所有可以使用的数据库
sp_databases
使用sp_helpfile可以查看当前数据库上所有文件的信息,如果指定文件的名称,则返回该文件的信息
use
pubs
sp_helpfile
sp_helpfile pubs_log -- pubs_log是该数据库的日志文件
sp_helpfile
sp_helpfile pubs_log -- pubs_log是该数据库的日志文件
使用sp_helpfilegroup可以查看所有文件组的信息,如果指定文件组的名字,则返回该文件组的信息
sp_helpfilegroup
sp_helpfilegroup own_group
sp_helpfilegroup own_group
4.修改数据库配置
最典型的是存储过程sp_dboption了,可以查看能设置的所有项,指定特定项则能修改其属性
sp_dboption
'
pubs
'
,
'
select into/bulkcopy
'
,
'
false
'
5.修改数据库的大小
修改数据库的大小实质上是修改数据文件和日志文件的长度,或者增加/删除操作系统文件来实现的
增加数据文件
--
这个例子在Company数据库的默认文件组PRIMARY文件组里,增加了一个数据文件Pri3_dat
ALTER DATABASE Company
ADD FILE
(
NAME = Pri3_dat,
FILENAME = ' e:program files/Microsoft sql server/mssql/data/Pri3dat.ndf ' ,
SIZE = 5 ,
MAXSIZE = 20 ,
FILEGROWTH = 5
)
ALTER DATABASE Company
ADD FILE
(
NAME = Pri3_dat,
FILENAME = ' e:program files/Microsoft sql server/mssql/data/Pri3dat.ndf ' ,
SIZE = 5 ,
MAXSIZE = 20 ,
FILEGROWTH = 5
)
以上创建的新数据文件,被放在了默认文件组PRIMARY里,如果要创建在别的文件组,则要加上TO FILEGROUP命令
--
这个例子增加一个数据文件Pri4_dat至组ComGroup1里
ALTER DATABASE Company
ADD FILE
(
NAME = Pri4_dat,
FILENAME = ' e:program files/Microsoft sql server/mssql/data/Pri4dat.ndf ' ,
SIZE = 5 ,
MAXSIZE = 20 ,
FILEGROWTH = 5
)
TO FILEGROUP ComGroup1
ALTER DATABASE Company
ADD FILE
(
NAME = Pri4_dat,
FILENAME = ' e:program files/Microsoft sql server/mssql/data/Pri4dat.ndf ' ,
SIZE = 5 ,
MAXSIZE = 20 ,
FILEGROWTH = 5
)
TO FILEGROUP ComGroup1
设置某一文件组为默认文件组
ALTER
DATABASE
Company
MODIFY FILEGROUP ComGroup1 DEFAULT
MODIFY FILEGROUP ComGroup1 DEFAULT
增加数据库文件大小(同时可以修改最大容量与增长率)
ALTER
DATABASE
Company
MODIFY FILE
(
NAME = Pri3_dat,
SIZE = 22 ,
MAXSIZE = 55 ,
FILEGROWTH = 3 %
)
MODIFY FILE
(
NAME = Pri3_dat,
SIZE = 22 ,
MAXSIZE = 55 ,
FILEGROWTH = 3 %
)
删除数据库文件
ALTER
DATABASE
Company
REMOVE FILE Pri4_dat
REMOVE FILE Pri4_dat