在SQL Server中,使用Create Database创建数据库,使用Alter Database命令,能够修改数据库的数据文件和日志文件。
一,创建数据库
1,在创建数据库时,最佳实践是:
- 创建一个或多个文件组,并设置默认文件组
- 每个文件组中的数据文件和CPU的内核数据相同,将文件均匀分布在不同的物理硬盘上,使IO均匀分布在不同的物理磁盘上;
- 数据文件的初始大小,文件增长和最大大小保持一致,这样,能够使每个文件的IO次数相对均匀;
- 日志文件分配在性能最好的物理硬盘上,写日志的性能直接影响数据修改的性能;
- 在创建文件时,为每个文件分配足够大的初始空间,避免数据文件size的增长
创建数据库的示例:
create database newdbon primary(name='newdb', filename='D:\MSSQLServer\newdb.mdf', size=1GB,maxsize=10GB,filegrowth=1GB),(name='newdb_1',filename='D:\MSSQLServer\newdb_1.ndf',size=1GB,maxsize=10GB,filegrowth=1GB),(name='newdb_2',filename='D:\MSSQLServer\newdb_2.ndf',size=1GB,maxsize=10GB,filegrowth=1GB),(name='newdb_3',filename='D:\MSSQLServer\newdb_3.ndf',size=1GB,maxsize=10GB,filegrowth=1GB),(name='newdb_4',filename='D:\MSSQLServer\newdb_4.ndf',size=1GB,maxsize=10GB,filegrowth=1GB)log on(name='newdb_log',filename='D:\MSSQLServer\newdb_log.ldf',size=1GB,maxsize=10GB,filegrowth=1GB)go
参数name指定逻辑文件名(logical_file_name),参数FileName指定物理文件名,SQL Server通过逻辑文件名引用数据文件或日志文件。
2,查看当前数据库的文件
use newdbgoselect df.file_id, df.type, df.type_desc, df.name, df.physical_name, df.state, df.state_desc, df.size*8/1024/1024 as size_gb, df.max_size*8/1024/1024 as max_size_gb, df.growth, df.is_percent_growth, fg.name as filegroup_name, fg.is_defaultfrom sys.database_files df inner join sys.filegroups fg on df.data_space_id=fg.data_space_id
View Code
3,查看所有数据库的文件
select DB_NAME(mf.database_id) as dbname, mf.file_id, mf.type as file_type, mf.type_desc, mf.data_space_id as file_group, mf.name as logical_file_name, mf.physical_name, mf.state_desc, mf.size*8/1024/1024 as size_gb, mf.max_size*8/1024/1024 as max_size_gb, mf.growth, mf.is_percent_growthfrom sys.master_files mf --where database_id=db_id('newdb')
View Code
二,增加数据/日志文件
1,增加文件组(File Group)
use master go alter database newdbadd filegroup fg_newdb1;
2,向fg_newdb1文件组(File Group)中增加数据文件
alter database newdbadd file(name=fg_newdb1_1,filename ='d:\MSSQLServer\fg_newdb1_1.ndf',size=1GB,maxsize=10GB,filegrowth=1GB)to filegroup fg_newdb1;
3,增加日志文件
alter database newdbadd log file(name=newdb_log_1,filename ='d:\MSSQLServer\newdb_log_1.ldf',size=1GB,maxsize=10GB,filegrowth=1GB);
三,修改数据库文件
1,修改数据文件的逻辑文件名(Logical file name)
MODIFY FILE ( NAME = ‘logical_file_name’, NEWNAME = ‘new_logical_name’ )
示例,将数据文件fg_newdb1_1 的逻辑文件名修改 fg_newdb1_file1
alter database newdbmodify file(NAME ='fg_newdb1_1',newname='fg_newdb1_file1')
2,修改数据文件的物理文件名
MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
修改数据文件的物理文件名,能够将数据文件移动到不同的位置上,分四步实现:
step1,在数据库中注册数据文件的新的物理文件名
alter database newdbmodify file(name='fg_newdb1_file1',filename='F:\MSSQLServer\fg_newdb1_file1.ndf');
语句执行成功之后,数据库会显示:物理文件名已经在系统目录中修改
The file "fg_newdb1_file1" has been modified in the system catalog. The new path will be used the next time the database is started.
step2,使数据库离线
use mastergo alter database newdbset offlinewith rollback immediate;
在将数据库离线(脱机,take offline)时,如果数据中有Sleeping Session存在,或者有query正在执行,那么Take Offline操作会一直等待下去,直到没有任何query运行,使用 ROLLBACK IMMEDIATE 选项,能够立即回滚数据库中正在执行的所有事务,使Take Offline 操作迅速完成。
step3,将数据文件移动到新的位置上
以管理员权限运行move命令将文件移动到新的位置上,并重命名为新的物理文件名
move D:\MSSQLServer\fg_newdb1_1.ndf F:\MSSQLServer\fg_newdb1_file1.ndf
step4,使数据库联机
use mastergo alter database newdbset online
四,从数据库中移除数据文件
在SQL Server中,不管是日志文件,还是数据文件,在删除之前,必须清空,只有空的文件才能从数据库中删除。
1,从数据库中删除数据文件
step1,清空数据文件
USE [db_study]GODBCC SHRINKFILE (N'db_study_file1' , EMPTYFILE)GO
Step2, 从数据库中删除数据文件
USE master;GOALTER DATABASE db_studyREMOVE FILE db_study_file1;GO
2,从数据库中删除日志文件
step1,清空日志文件
USE [db_study]GODBCC SHRINKFILE (N'db_study_log1' , EMPTYFILE)GO
step2,从数据库中删除日志文件
USE master;GOALTER DATABASE db_studyREMOVE FILE db_study_log1;GO
3,从数据库中删除文件组
USE master;GOALTER DATABASE db_studyREMOVE FILEGROUP fg_study1;GO
参考文档:
CREATE DATABASE (Transact-SQL)
ALTER DATABASE File and Filegroup Options (Transact-SQL)
原标题:DB1:数据库的创建和文件的修改
关键词:数据库