你的位置:首页 > 数据库

[数据库]DB1:数据库的创建和文件的修改


在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)