sqlserver：文件和文件组

环境：

• window10 x64 专业版
• sqlserver2014

参考：
官网：文件和文件组体系结构
sql server 里的文件和文件组使用
SQL Server中数据库文件的存放方式，文件和文件组
SQL Server 文件和文件组

一、什么是文件和文件组？

我们默认新建一个数据库[test]，sqlserver就会给我们生成两个文件：test.mdf和test.ldf
这两个文件就称之为数据库文件。
数据库文件按存储的数据类型分为两类：一类是存储数据，比如说：test.mdf，另一类存储事务日志，比如说：test.ldf
那么，一个数据库只能有这两个文件吗？请考虑下面的问题：

• 数据很大，磁盘空间不够用怎么办（比如说：数据库有150G，而存储的磁盘只有100G）？
• 对一个150G的test.mdf文件进行读写，速度跟的上吗？
• 如果，我只想对数据库中的部分表进行备份和恢复，面对150G的数据库该怎么办（用数据文件进行备份和还原比较麻烦，但提供了大数据量操作的可能性）？

基于上面的问题，我们肯定能想到：把数据文件拆分啊！对的，sqlserver允许一个数据库有多个数据文件和多个日志文件，并且将它们进行了逻辑分组（日志文件不在这个分组内），称之为：文件组。
总结来说，它们具有如下的体系：

从上图也可以看出，其实存储数据的文件又分为两类：主文件和次要文件，主文件只能有一个，而次要文件可以有多个。
数据库的文件组可以有多个，但只能有一个默认的，我们以默认方式新建了test数据库后，它就有一个默认的文件组：PRIMARY，里面有一个主文件test.mdf（test.ldf是日志文件，不在文件组里，是单独存储的）。

另外：

• 我们在数据库上无论新建表/索引等都是指定的文件组（没指定用默认的），而不是指定的数据文件。
• 文件组内的数据是均匀分布在组内的各个数据文件中的。

二、应该怎么使用文件组和文件？

2.1 新建文件组和文件

我们可以在创建数据库的时候创建，也可以创建后再修改。无论哪种方式，我们都可以通过sql语句或者sqlserver客户端操作。
下面我给test数据库添加文件组和文件：

2.2 查看创建的文件组和文件

使用下面的命令查看：

2.3 在指定的文件组中创建表

2.4 给表test造数据，查看数据分布情况

在造数据之前，我们观察下文件组group1的数据文件情况：

可以看到，这两个数据文件的大小都是初始值：5M
开始造数据：

现在，我们再来看下文件组内数据文件的存储情况：

三、数据库的备份和恢复可以直接操作文件组

这个操作起来比较复杂，可以参考：
sql server 备份与恢复系列六 文件组备份与还原

查看数据库备份和还原的记录如下：
备份记录：

SELECT 
     bs.backup_set_id,
     bs.database_name,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
GO

还原记录：

SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
GO