百度360必应搜狗淘宝本站头条
python判断字典是否为空crontab每周一执行aes和des区别安装指定版本npmadb pull
当前位置：网站首页 > IT知识 > 正文

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

liuian 2025-03-24 17:59 41 浏览

一.背景（Contexts）

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

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

二.解决方案（Solution）

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

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

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

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

F1_文件组搬迁逻辑图

三.搬迁步骤（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:        
-- 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

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

F4_备份文件列表

(二) 下面是用于生成还原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:        
-- 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文件对应，所以是不需要理会这个文件组中包含了多少个逻辑分区；

相关推荐

驱动网卡（怎么从新驱动网卡）
驱动网卡（怎么从新驱动网卡）

网卡一般是指为电脑主机提供有线无线网络功能的适配器。而网卡驱动指的就是电脑连接识别这些网卡型号的桥梁。网卡只有打上了网卡驱动才能正常使用。并不是说所有的网卡一插到电脑上面就能进行数据传输了，他都需要里面芯片组的驱动文件才能支持他进行数据传输...

2026-01-30 00:37 liuian

win10更新助手装系统（微软win10更新助手）

1、点击首页“系统升级”的按钮，给出弹框，告诉用户需要上传IMEI码才能使用升级服务。同时给出同意和取消按钮。华为手机助手2、点击同意，则进入到“系统升级”功能华为手机助手华为手机助手3、在检测界面，...

windows11专业版密钥最新（windows11专业版激活码永久）

 Windows11专业版的正版密钥，我们是对windows的激活所必备的工具。该密钥我们可以通过微软商城或者通过计算机的硬件供应商去购买获得。获得了windows11专业版的正版密钥后，我...

手机删过的软件恢复（手机删除过的软件怎么恢复）
手机删过的软件恢复（手机删除过的软件怎么恢复）

操作步骤：1、首先，我们需要先打开手机。然后在许多图标中找到带有[文件管理]文本的图标，然后单击“文件管理”进入页面。2、进入页面后，我们将在顶部看到一行文本：手机，最新信息，文档，视频，图片，音乐，收藏，最后是我们正在寻找的[更多]，单击...

2026-01-29 23:55 liuian

一键ghost手动备份系统步骤（一键ghost 备份）

　　步骤1、首先把装有一键GHOST装系统的U盘插在电脑上，然后打开电脑马上按F2或DEL键入BIOS界面，然后就选择BOOT打USDHDD模式选择好，然后按F10键保存，电脑就会马上重启。　　步骤...

怎么创建局域网（怎么创建局域网打游戏）

　　1、购买路由器一台。进入路由器把dhcp功能打开　　2、购买一台交换机。从路由器lan端口拉出一条网线查到交换机的任意一个端口上。　　3、两台以上电脑。从交换机任意端口拉出网线插到电脑上（电脑设置...

精灵驱动器官方下载（精灵驱动手机版下载）

是的。驱动精灵是一款集驱动管理和硬件检测于一体的、专业级的驱动管理和维护工具。驱动精灵为用户提供驱动备份、恢复、安装、删除、在线更新等实用功能。1、全新驱动精灵2012引擎，大幅提升硬件和驱动辨识能力...

一键还原系统步骤（一键还原系统有哪些）

1、首先需要下载安装一下Windows一键还原程序，在安装程序窗口中，点击“下一步”，弹出“用户许可协议”窗口，选择“我同意该许可协议的条款”，并点击“下一步”。　　2、在弹出的“准备安装”窗口中，可...

电脑加速器哪个好（电脑加速器哪款好）

我认为pp加速器最好用，飞速土豆太懒，急速酷六根本不工作。pp加速器什么网页都加速，太任劳任怨了！以上是个人观点，具体性能请自己试。ps：我家电脑性能很好。迅游加速盒子是可以加速电脑的。因为有过之...

任何u盘都可以做启动盘吗（u盘必须做成启动盘才能装系统吗）

是的，需要注意，U盘的大小要在4G以上，最好是8G以上，因为启动盘里面需要装系统，内存小的话，不能用来安装系统。内存卡或者U盘或者移动硬盘都可以用来做启动盘安装系统。普通的U盘就可以，不过最好U盘...

u盘怎么恢复文件（u盘文件恢复的方法）

开360安全卫士，点击上面的“功能大全”。点击文件恢复然后点击“数据”下的“文件恢复”功能。选择驱动接着选择需要恢复的驱动，选择接入的U盘。点击开始扫描选好就点击中间的“开始扫描”，开始扫描U盘数据。...

系统虚拟内存太低怎么办（系统虚拟内存占用过高什么原因）

1.检查系统虚拟内存使用情况，如果发现有大量的空闲内存，可以尝试释放一些不必要的进程，以释放内存空间。2.如果系统虚拟内存使用率较高，可以尝试增加系统虚拟内存的大小，以便更多的应用程序可以使用更多...

剪贴板权限设置方法（剪贴板访问权限）
剪贴板权限设置方法（剪贴板访问权限）

1、首先打开iphone手机，触碰并按住单词或图像直到显示选择选项。2、其次，然后选取“拷贝”或“剪贴板”。3、勾选需要的“权限”，最后选择开启，即可完成苹果剪贴板权限设置。仅参考1.打开苹果手机设置按钮,点击【通用】。2.点击【键盘】,再...

2026-01-29 21:37 liuian

平板系统重装大师（平板重装win系统）

如果你的平板开不了机，但可以连接上电脑，那就能好办，楼主下载安装个平板刷机王到你的个人电脑上，然后连接你的平板，平板刷机王会自动识别你的平板，平板刷机王上有你平板的我刷机包，楼主点击下载一个，下载完成...

联想官网售后服务网点（联想官网售后服务热线）

联想3c服务中心是联想旗下的官方售后，是基于互联网O2O模式开发的全新服务平台。可以为终端用户提供多品牌手机、电脑以及其他3C类产品的维修、保养和保险服务。根据客户需求层次，联想服务针对个人及家庭客户...

一周热门
最近发表
标签列表