数据库的创建和文件的修改

在SQL Server中，使用Create Database创建数据库，使用Alter Database命令，能够修改数据库的数据文件和日志文件。

一，创建数据库

1，在创建数据库时，最佳实践是：

• 创建一个或多个文件组，并设置默认文件组
• 每个文件组中的数据文件和CPU的内核数据相同，将文件均匀分布在不同的物理硬盘上，使IO均匀分布在不同的物理磁盘上；
• 数据文件的初始大小，文件增长和最大大小保持一致，这样，能够使每个文件的IO次数相对均匀；
• 日志文件分配在性能最好的物理硬盘上，写日志的性能直接影响数据修改的性能；
• 在创建文件时，为每个文件分配足够大的初始空间，避免数据文件size的增长

创建数据库的示例:

create database newdb
on 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 newdb
go

select 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_default
from sys.database_files df 
inner join sys.filegroups fg
    on df.data_space_id=fg.data_space_id

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_growth
from sys.master_files mf 
--where database_id=db_id('newdb')

二，增加数据/日志文件

1，增加文件组（File Group）

use master 
go 
alter database newdb
add filegroup fg_newdb1;

2，向fg_newdb1文件组（File Group）中增加数据文件

alter database newdb
add 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 newdb
add 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 newdb
modify 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 newdb
modify 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 master
go 
alter database newdb
set offline
with 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 master
go 
alter database newdb
set online

四，从数据库中移除数据文件

在SQL Server中，不管是日志文件，还是数据文件，在删除之前，必须清空，只有空的文件才能从数据库中删除。

1，从数据库中删除数据文件

step1，清空数据文件

USE [db_study]
GO
DBCC SHRINKFILE (N'db_study_file1' , EMPTYFILE)
GO

Step2, 从数据库中删除数据文件

USE master;
GO
ALTER DATABASE db_study
REMOVE FILE db_study_file1;
GO

2，从数据库中删除日志文件

step1，清空日志文件

USE [db_study]
GO
DBCC SHRINKFILE (N'db_study_log1' , EMPTYFILE)
GO

step2，从数据库中删除日志文件

USE master;
GO
ALTER DATABASE db_study
REMOVE FILE db_study_log1;
GO

3，从数据库中删除文件组

USE master;
GO
ALTER DATABASE db_study
REMOVE FILEGROUP fg_study1;
GO

 

参考文档：

CREATE DATABASE (Transact-SQL)

ALTER DATABASE File and Filegroup Options (Transact-SQL)