一.本文所涉及的内容（Contents）

本文所涉及的内容（Contents）
背景（Contexts）
解决方案（Solution）
搬迁步骤（Procedure）
搬迁脚本（SQL Codes）
注意事项（Attention）
疑问（Questions）
参考文献（References）

二.背景（Contexts）

　　有一个数据库大概在700G左右，需要从服务器A搬迁到服务器B，两台服务器网络传输速度可以达到8MB/s，怎么做才能更快的搬迁并且宕机时间最短呢？

　　数据库业务逻辑概述：这个数据库只会插入数据，每天大概有300W条数据，不会对数据进行修改，只有一个表比较大，并且这个表是以自增ID作为分区依据列的，文件组会被重用，数据库为简单恢复模式，我定时会对表数据进行交换分区删除数据；

三.解决方案（Solution）

之前我也写过关于搬迁数据库的一些文章：

　　1. SQL Server 数据库最小宕机迁移方案，这篇文章是通过完全备份+差异备份的方式迁移数据库的，这种方式比较合适数据库只有20G左右的数据库，宕机时间=差异备份时间+传输差异备份时间+还原差异备份时间，一般来说这个时间都比较短，因为差异备份都不会太大；

　　2. SQL Server 数据库迁移偏方，这篇文章是通过作业的方式迁移数据库的，一个事务中转移N条（大约2W条）数据，N值可以通过测试进行调整（需要看网络情况而定），这种方式比较适合数据库比较大，比如几百G的数据库，而且网络环境比较差的情况下，宕机时间≈0（当转移最后一部分数据足够小），缺点就是迁移的时间会比较长；

　　3. 那么这篇文章我们再来讲讲其它方式的迁移，在上面提到的背景下，可以通过对分区文件组进行备份的方式迁移数据库，这种方式比较适合大数据库的迁移，宕机时间=最后一个文件组备份时间+传输备份时间+还原最后一个文件组时间，缺点是宕机时间会比较大，但是整体迁移的时间会比较小；下面是逻辑结构图：

（Figure1：文件组搬迁逻辑图）

四.搬迁步骤（Procedure）

　　在讲述搬迁步骤之前，我们首先来看看文件组的大体情况，通过下面的SQL语句可以查看文件组的相关信息，见Figure2、Figure3；

--查看文件组信息
SELECT df.[name], df.physical_name, df.[size], df.growth, fg.[name]
[filegroup], fg.is_default
FROM sys.database_files df
JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id

（Figure2：文件组列表）

（Figure3：文件组列表）

下面就讲讲搬迁的步骤：

1. 首先我们先清理下数据，把不必要的数据通过交换分区的方式交换出去；

2. 查看这张大表当前的自增ID值，通过修改分区方案让新插入的数据存入到一个空的文件组（因为空的文件组在最后备份会更小一点），很多情况下，文件组是会重用的，所以要注意这个文件组是空的；

3. 设置数据库为完整恢复模式；

4. 备份除了上面提到的文件组，如果条件允许可以进行备份的压缩；（动态生成SQL）

5. 通过FTP传输备份文件到新的服务器；

6. 备份主分区，需要确保这个时候不会对主分区的数据进行修改，并传输主分区备份文件；

7. 先还原主分区的备份，再还原上面的文件组备份；（动态生成SQL）

8. 对最后一个文件组进行备份，对日志进行备份，对没有做分区对齐的索引文件组进行备份，把这3个备份传输到新服务器；

9. 还原文件组，还原日志；

五.搬迁脚本（SQL Codes）

搬迁脚本包括两个部分，一个备份使用的脚本，一个是还原使用的脚本：

1. 备份脚本，根据分区情况来自动生成对应的备份脚本；

2. 还原脚本，根据分区情况和备份文件的规则来生成对应的还原脚本，也就是说还原脚本是依据备份脚本的；

(一) 下面是用于生成备份SQL的代码，这个代码需要提供两个变量值：

1. @DataBaseName指定需要进行备份的数据库名，值为'Barefoot.Archives'；

2. @BackupPath在旧服务器本地备份文件组存放的地址，值为：'E:\DBBackup\'；

在旧数据库Barefoot.Archives中执行下面的SQL脚本：

-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2014/02/26>
-- Description: <生成分区备份脚本>
-- =============================================
DECLARE @DataBaseName SYSNAME--数据库名称
DECLARE @BackupPath SYSNAME--保存分区备份的路径
DECLARE @FilegroupName SYSNAME--分区文件组名称
DECLARE @sql NVARCHAR(MAX)--sql字符串

--设置下面变量
SET @DataBaseName = 'DataBaseName'
SET @BackupPath = 'D:\DBBackup\'

--1.设置完整模式
PRINT '--设置完整模式'
SET @sql = 'USE [master]
GO
ALTER DATABASE ['+@DataBaseName +'] SET RECOVERY FULL WITH NO_WAIT
GO'
PRINT @sql + CHAR(13)

--2.备份分区
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
    SELECT [name] FROM sys.filegroups ORDER BY is_default

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @FilegroupName
WHILE @@FETCH_STATUS=0
BEGIN
    --逻辑处理
    PRINT '--备份分区- ' + @FilegroupName
    SET @sql = 'BACKUP DATABASE [' + @DataBaseName + ']
FILEGROUP = ''' + @FilegroupName + '''
TO DISK = ''' + @BackupPath+@FilegroupName + '.bak'' WITH FORMAT
GO'
    PRINT @sql + CHAR(13)
    
    FETCH NEXT FROM @itemCur INTO @FilegroupName
END 

CLOSE @itemCur
DEALLOCATE @itemCur

--3.备份日志
PRINT '--备份日志'
SET @sql = 'BACKUP LOG [' + @DataBaseName + ']
TO DISK = ''' + @BackupPath+@DataBaseName + '_Log.bak'' WITH FORMAT
GO'
PRINT @sql + CHAR(13)

上面SQL脚本的逻辑是：

1. 首先设置数据库的恢复模式为完整恢复模式，这是为了后面对数据库的日志进行备份；

2. 通过当前数据库的系统表sys.filegroups拿到文件组的名称，这里把默认文件排在最后面，这是因为有可能会对配置表进行的操作，所以把这个文件组放到最后备份；

3. 使用游标的方式来循环文件组，生成文件组对应的备份SQL语句；

4. 最后备份数据库的日志，对文件组的还原是需要通过日志备份才能还原的；

　　在旧数据库执行上面的SQL脚本，将会产生生成下面的SQL（只保留了部分SQL）：

--设置完整模式
USE [master]
GO
ALTER DATABASE [DataBaseName] SET RECOVERY FULL WITH NO_WAIT
GO

--备份分区- FG_Archive_Id_01
BACKUP DATABASE [DataBaseName]
FILEGROUP = 'FG_Archive_Id_01'
TO DISK = 'D:\DBBackup\FG_Archive_Id_01.bak' WITH FORMAT
GO

--备份分区- FG_Archive_Id_02
BACKUP DATABASE [DataBaseName]
FILEGROUP = 'FG_Archive_Id_02'
TO DISK = 'D:\DBBackup\FG_Archive_Id_02.bak' WITH FORMAT
GO

--备份分区- FG_Archive_Index
BACKUP DATABASE [DataBaseName]
FILEGROUP = 'FG_Archive_Index'
TO DISK = 'D:\DBBackup\FG_Archive_Index.bak' WITH FORMAT
GO

--备份分区- PRIMARY
BACKUP DATABASE [DataBaseName]
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\DBBackup\PRIMARY.bak' WITH FORMAT
GO

--备份日志
BACKUP LOG [DataBaseName]
TO DISK = 'D:\DBBackup\Barefoot.Archives_Log.bak' WITH FORMAT
GO

执行完上面的脚本，会生成下图所示的备份文件：

（Figure4：备份文件列表）

(二) 下面是用于生成还原SQL的代码，这个代码需要提供几个变量值：

1. @DataBaseName指定需要进行备份的数据库名，值为'Barefoot.Archives'；

2. @BackupPath在新服务器文件组备份的地址，值为：'E:\DBBackup\'；

3. @SavePath_Drive存在数据文件的盘符，值为：'F:\'；

4. @SavePath_FolderName存放数据文件的文件夹，值为：'DataBase\'；

5. @SavePath_SubFolderName存放ndf文件的文件夹，值为：'FG_Archive\'；

6. @IsSamePath表示是否延续之前的physical_name值，值为1表示延续，这样会使用@SavePath_Drive替换physical_name的盘符，这样@SavePath_FolderName和@SavePath_SubFolderName就不会起作用了，值为0表示不延续，这样physical_name的值=@SavePath_Drive+@SavePath_FolderName+@SavePath_SubFolderName；

在旧数据库Barefoot.Archives中执行下面的SQL脚本：

-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2014/02/26>
-- Description: <生成分区还原脚本>
-- =============================================
DECLARE @DataBaseName SYSNAME--数据库名称
DECLARE @BackupPath SYSNAME--保存备份文件的路径
DECLARE @SavePath_Drive SYSNAME--保存数据库文件的盘符
DECLARE @SavePath_FolderName SYSNAME--保存数据库的文件夹
DECLARE @SavePath_SubFolderName SYSNAME--保存分区的文件夹
DECLARE @FilegroupName SYSNAME--分区文件组名称
DECLARE @FileName SYSNAME--分区文件名称
DECLARE @PhysicalName SYSNAME--物理路径
DECLARE @IsSamePath INT--是否跟远路径一样1,0
DECLARE @sql NVARCHAR(MAX)--sql字符串

--设置下面变量
SET @DataBaseName = 'DataBaseName'
SET @BackupPath = 'E:\DBBackup\'
SET @SavePath_Drive = 'F:\'
SET @SavePath_FolderName = 'DataBase\'
SET @SavePath_SubFolderName = 'FG_Archive\'
SET @IsSamePath = 1

--1.还原主分区
SELECT @FilegroupName = [name] FROM sys.filegroups WHERE is_default = 1
PRINT '--还原主分区'
SET @sql = 'RESTORE DATABASE [' + @DataBaseName + ']
FILEGROUP = ''' + @FilegroupName + '''
FROM DISK = ''' + @BackupPath + @FilegroupName + '.bak'' WITH FILE = 1, 
MOVE N''' + @DataBaseName + ''' TO N''' + @SavePath_Drive + @SavePath_FolderName + @DataBaseName + '.mdf'',  
MOVE N''' + @DataBaseName + '_log'' TO N''' + @SavePath_Drive + @SavePath_FolderName + @DataBaseName + '_log.ldf'',
NORECOVERY,REPLACE,STATS = 10
GO'
PRINT @sql + CHAR(13)

--2.还原分区
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
    SELECT df.[name] AS FileName, df.physical_name, fg.[name] AS FilegroupName
        FROM sys.database_files df
        JOIN sys.filegroups fg
        ON df.data_space_id = fg.data_space_id
     WHERE fg.is_default = 0

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @FileName,@PhysicalName,@FilegroupName
WHILE @@FETCH_STATUS=0
BEGIN
    --逻辑处理
    PRINT '--还原分区- ' + @FilegroupName
    IF @IsSamePath = 0
        SET @PhysicalName = @SavePath_Drive + @SavePath_FolderName + @SavePath_SubFolderName + '\' + @FileName + '.ndf'
    ELSE
        SET @PhysicalName = @SavePath_Drive + SUBSTRING(@PhysicalName,CHARINDEX('\',@PhysicalName)+1,LEN(@PhysicalName))
    SET @sql = 'RESTORE DATABASE [' + @DataBaseName + ']
FILEGROUP = ''' + @FilegroupName + '''
FROM DISK = ''' + @BackupPath+@FilegroupName + '.bak'' WITH FILE = 1, 
MOVE N''' + @FileName + ''' 
TO N''' + @PhysicalName + ''',
NORECOVERY
GO'
    PRINT @sql + CHAR(13)
    
    FETCH NEXT FROM @itemCur INTO @FileName,@PhysicalName,@FilegroupName
END 

CLOSE @itemCur
DEALLOCATE @itemCur

--3.还原日志
PRINT '--还原日志'
SET @sql = 'RESTORE LOG [' + @DataBaseName + ']
FROM DISK = ''' + @BackupPath + @DataBaseName + '_Log.bak''
WITH NORECOVERY
GO'
PRINT @sql + CHAR(13)

--4.还原在线
PRINT '--还原在线'
SET @sql = 'RESTORE DATABASE [' + @DataBaseName + ']
WITH RECOVERY
GO'
PRINT @sql + CHAR(13)

上面SQL脚本的逻辑是：

1. 通过系统表sys.filegroups找到默认文件组，先还原这个主文件；

2. 使用游标的方式来循环系统表sys.filegroups，拿到文件组名称，生成文件组对应的还原SQL语句；

3. 接着还原数据库的日志；

4. 最后还原在线，让数据库在线；

执行上面的SQL脚本，将会产生生成下面的SQL（只保留了部分SQL）：

--还原主分区
RESTORE DATABASE [DataBaseName]
FILEGROUP = 'PRIMARY'
FROM DISK = 'E:\DBBackup\PRIMARY.bak' WITH FILE = 1, 
MOVE N'Barefoot.Archives' TO N'F:\DataBase\Barefoot.Archives.mdf',  
MOVE N'Barefoot.Archives_log' TO N'F:\DataBase\Barefoot.Archives_log.ldf',
NORECOVERY,REPLACE,STATS = 10
GO

--还原分区- FG_Archive_Id_01
RESTORE DATABASE [DataBaseName]
FILEGROUP = 'FG_Archive_Id_01'
FROM DISK = 'E:\DBBackup\FG_Archive_Id_01.bak' WITH FILE = 1, 
MOVE N'FG_Archive_Id_01_data' 
TO N'F:\DataBase\FG_Archive\FG_Archive_Id_01_data.ndf',
NORECOVERY
GO

--还原分区- FG_Archive_Id_02
RESTORE DATABASE [DataBaseName]
FILEGROUP = 'FG_Archive_Id_02'
FROM DISK = 'E:\DBBackup\FG_Archive_Id_02.bak' WITH FILE = 1, 
MOVE N'FG_Archive_Id_02_data' 
TO N'F:\DataBase\FG_Archive\FG_Archive_Id_02_data.ndf',
NORECOVERY
GO

--还原分区- FG_Archive_Index
RESTORE DATABASE [DataBaseName]
FILEGROUP = 'FG_Archive_Index'
FROM DISK = 'E:\DBBackup\FG_Archive_Index.bak' WITH FILE = 1, 
MOVE N'FG_Archive_Index_data' 
TO N'F:\DataBase\Barefoot.Archives\FG_Archive_Index_data.ndf',
NORECOVERY
GO

--还原日志
RESTORE LOG [DataBaseName]
FROM DISK = 'E:\DBBackup\Barefoot.Archives_Log.bak'
WITH NORECOVERY
GO

--还原在线
RESTORE DATABASE [DataBaseName]
WITH RECOVERY
GO

在新服务器上执行上面的SQL脚本还原数据库，需要注意的是：在还原在线之前数据库都是一直处于：正在还原的状态的；

六.注意事项（Attention）

1. 在实际运用中，可以结合本文和SQL Server 数据库迁移偏方进行灵活结合运用，当通过本文件组备份后，旧库继续进数据，在花销时间最大的网络传输过程和还原过程继续对老库进数据，这样当还原好数据库之后使用SQL Server 数据库迁移偏方来转移最新的数据，这样宕机的时间会趋向于0；

2. 其实为了确保某些文件组不被修改，可以设置文件组的只读属性，这样可以确保只有某个文件组在进新数据，可惜的是设置了只读也无法拷贝这些文件组文件通过FTP传输，提示：操作无法完成，因为文件已在SQL Server（MSSQLSERVER）中打开。

3. 上面脚本的每个文件组中只包含了一个文件，如果一个文件组包含多个文件，那就需要修改下脚本了；

4. 高文佳曾经说过，可以先删除索引，再压缩备份，还原之后再创建索引，是的，这不防是一个好方法，不过需要考虑两点，一个是在还原之后创建索引的速度与时间，如果磁盘速度不算快，那你就要考虑删除索引是否适合了；另外一点是你的数据库是否能停机让你删除索引，这个跟具体的业务有关；

七.疑问（Questions）

　　1. 对primary进行完整文件组备份（作为基备份），对FG1进行完整文件组备份（作为基备份）这些描述有问题吧？对primary进行完整文件组备份应该不会生成基线的吧？ SQL文件组备份和还原

　　2. 如果在同一个文件组中有两个以上的分区值，就是把两个段的分区方案中同指向同一个分区文件组，那在备份和还原有什么需要注意的呢？能成功备份还原嘛？

--备份分区
DECLARE @FileName VARCHAR(200)
SET @FileName = 'G:\DBBackup\FG_Archive_Id_05_null.bak'
BACKUP DATABASE [DataBaseName]
FILEGROUP='FG_Archive_Id_05' TO DISK=@FileName WITH FORMAT
GO

--还原分区
RESTORE DATABASE [DataBaseName]
FILEGROUP='FG_Archive_Id_05' FROM DISK='E:\DBBackup\FG_Archive_Id_05_null.bak' WITH  FILE = 1, 
MOVE N'FG_Archive_Id_05_data' TO N'E:\DataBase\FG_Archive\FG_Archive_Id_05_data.ndf',  
NORECOVERY
GO

　　解答：从备份和还原的代码可以看出只是把FILEGROUP与bak对应，与ndf文件对应，所以是不需要理会这个文件组中包含了多少个逻辑分区；

八.参考文献（References）

SQL文件组备份和还原

Sql Server 系统表分析(1) - 备份表

如何还原到故障点 (Transact-SQL)

backupset (Transact-SQL)

SQL Server 大数据搬迁之文件组备份还原实战

一.本文所涉及的内容（Contents）

二.背景（Contexts）

三.解决方案（Solution）

四.搬迁步骤（Procedure）

五.搬迁脚本（SQL Codes）

六.注意事项（Attention）

七.疑问（Questions）

八.参考文献（References）

热门文章

最新文章

相关课程

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

SQL Server 大数据搬迁之文件组备份还原实战

一.本文所涉及的内容（Contents）

二.背景（Contexts）

三.解决方案（Solution）

四.搬迁步骤（Procedure）

五.搬迁脚本（SQL Codes）

六.注意事项（Attention）

七.疑问（Questions）

八.参考文献（References）

热门文章

最新文章

相关课程

相关电子书