代码改变世界

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

2014-03-05 11:15 by 听风吹雨, ... 阅读, ... 评论, 收藏, 编辑

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 解决方案(Solution)
  4. 搬迁步骤(Procedure)
  5. 搬迁脚本(SQL Codes)
  6. 注意事项(Attention)
  7. 疑问(Questions)
  8. 参考文献(References)

二.背景(Contexts)

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

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

三.解决方案(Solution)

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

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

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

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

F1_文件组搬迁逻辑图

(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

F2_fg1

(Figure2:文件组列表)

F3_fg2

(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

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

F4_备份文件列表

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