一.背景（Contexts）
有一個數(shù)據(jù)庫大概在700G左右，需要從服務(wù)器A搬遷到服務(wù)器B，兩臺服務(wù)器網(wǎng)絡(luò)傳輸速度可以達到8MB/s，怎么做才能更快的搬遷并且宕機時間最短呢？
數(shù)據(jù)庫業(yè)務(wù)邏輯概述：這個數(shù)據(jù)庫只會插入數(shù)據(jù)，每天大概有300W條數(shù)據(jù)，不會對數(shù)據(jù)進行修改，只有一個表比較大，并且這個表是以自增ID作為分區(qū)依據(jù)列的，文件組會被重用，數(shù)據(jù)庫為簡單恢復(fù)模式，我定時會對表數(shù)據(jù)進行交換分區(qū)刪除數(shù)據(jù)；
二.解決方案（Solution）
之前我也寫過關(guān)于搬遷數(shù)據(jù)庫的一些文章：
1. SQL Server 數(shù)據(jù)庫最小宕機遷移方案，這篇文章是通過完全備份+差異備份的方式遷移數(shù)據(jù)庫的，這種方式比較合適數(shù)據(jù)庫只有20G左右的數(shù)據(jù)庫，宕機時間=差異備份時間+傳輸差異備份時間+還原差異備份時間，一般來說這個時間都比較短，因為差異備份都不會太大；
2. SQL Server 數(shù)據(jù)庫遷移偏方，這篇文章是通過作業(yè)的方式遷移數(shù)據(jù)庫的，一個事務(wù)中轉(zhuǎn)移N條（大約2W條）數(shù)據(jù)，N值可以通過測試進行調(diào)整（需要看網(wǎng)絡(luò)情況而定），這種方式比較適合數(shù)據(jù)庫比較大，比如幾百G的數(shù)據(jù)庫，而且網(wǎng)絡(luò)環(huán)境比較差的情況下，宕機時間≈0（當(dāng)轉(zhuǎn)移最后一部分?jǐn)?shù)據(jù)足夠?。秉c就是遷移的時間會比較長；
3. 那么這篇文章我們再來講講其它方式的遷移，在上面提到的背景下，可以通過對分區(qū)文件組進行備份的方式遷移數(shù)據(jù)庫，這種方式比較適合大數(shù)據(jù)庫的遷移，宕機時間=最后一個文件組備份時間+傳輸備份時間+還原最后一個文件組時間，缺點是宕機時間會比較大，但是整體遷移的時間會比較??；下面是邏輯結(jié)構(gòu)圖：
F1_文件組搬遷邏輯圖
三.搬遷步驟（Procedure）
在講述搬遷步驟之前，我們首先來看看文件組的大體情況，通過下面的SQL語句可以查看文件組的相關(guān)信息，見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. 首先我們先清理下數(shù)據(jù)，把不必要的數(shù)據(jù)通過交換分區(qū)的方式交換出去；
2. 查看這張大表當(dāng)前的自增ID值，通過修改分區(qū)方案讓新插入的數(shù)據(jù)存入到一個空的文件組（因為空的文件組在最后備份會更小一點），很多情況下，文件組是會重用的，所以要注意這個文件組是空的；
3. 設(shè)置數(shù)據(jù)庫為完整恢復(fù)模式；
4. 備份除了上面提到的文件組，如果條件允許可以進行備份的壓縮；（動態(tài)生成SQL）
5. 通過FTP傳輸備份文件到新的服務(wù)器；
6. 備份主分區(qū)，需要確保這個時候不會對主分區(qū)的數(shù)據(jù)進行修改，并傳輸主分區(qū)備份文件；
7. 先還原主分區(qū)的備份，再還原上面的文件組備份；（動態(tài)生成SQL）
8. 對最后一個文件組進行備份，對日志進行備份，對沒有做分區(qū)對齊的索引文件組進行備份，把這3個備份傳輸?shù)叫路?wù)器；
9. 還原文件組，還原日志；
四.搬遷腳本（SQL Codes）
搬遷腳本包括兩個部分，一個備份使用的腳本，一個是還原使用的腳本：
1. 備份腳本，根據(jù)分區(qū)情況來自動生成對應(yīng)的備份腳本；
2. 還原腳本，根據(jù)分區(qū)情況和備份文件的規(guī)則來生成對應(yīng)的還原腳本，也就是說還原腳本是依據(jù)備份腳本的；
(一) 下面是用于生成備份SQL的代碼，這個代碼需要提供兩個變量值：
1. @DataBaseName指定需要進行備份的數(shù)據(jù)庫名，值為'Barefoot.Archives'；
2. @BackupPath在舊服務(wù)器本地備份文件組存放的地址，值為：'E:\DBBackup\'；
在舊數(shù)據(jù)庫Barefoot.Archives中執(zhí)行下面的SQL腳本：
-- =============================================-- Author: <聽風(fēng)吹雨>-- Blog: -- Create date: <2014/02/26>-- Description: <生成分區(qū)備份腳本>-- =============================================DECLARE @DataBaseName SYSNAME--數(shù)據(jù)庫名稱DECLARE @BackupPath SYSNAME--保存分區(qū)備份的路徑DECLARE @FilegroupName SYSNAME--分區(qū)文件組名稱DECLARE @sql NVARCHAR(MAX)--sql字符串--設(shè)置下面變量SET @DataBaseName = 'DataBaseName'SET @BackupPath = 'D:\DBBackup\'--1.設(shè)置完整模式PRINT '--設(shè)置完整模式'SET @sql = 'USE [master]GOALTER DATABASE ['+@DataBaseName +'] SET RECOVERY FULL WITH NO_WAITGO'PRINT @sql + CHAR(13)--2.備份分區(qū)DECLARE @itemCur CURSORSET @itemCur = CURSOR FOR SELECT [name] FROM sys.filegroups ORDER BY is_defaultOPEN @itemCurFETCH NEXT FROM @itemCur INTO @FilegroupNameWHILE @@FETCH_STATUS=0BEGIN --邏輯處理 PRINT '--備份分區(qū)- ' + @FilegroupName SET @sql = 'BACKUP DATABASE [' + @DataBaseName + ']FILEGROUP = ''' + @FilegroupName + '''TO DISK = ''' + @BackupPath+@FilegroupName + '.bak'' WITH FORMATGO' PRINT @sql + CHAR(13) FETCH NEXT FROM @itemCur INTO @FilegroupNameEND CLOSE @itemCurDEALLOCATE @itemCur--3.備份日志PRINT '--備份日志'SET @sql = 'BACKUP LOG [' + @DataBaseName + ']TO DISK = ''' + @BackupPath+@DataBaseName + '_Log.bak'' WITH FORMATGO'PRINT @sql + CHAR(13)復(fù)制代碼
上面SQL腳本的邏輯是：
1. 首先設(shè)置數(shù)據(jù)庫的恢復(fù)模式為完整恢復(fù)模式，這是為了后面對數(shù)據(jù)庫的日志進行備份；
2. 通過當(dāng)前數(shù)據(jù)庫的系統(tǒng)表sys.filegroups拿到文件組的名稱，這里把默認文件排在最后面，這是因為有可能會對配置表進行的操作，所以把這個文件組放到最后備份；
3. 使用游標(biāo)的方式來循環(huán)文件組，生成文件組對應(yīng)的備份SQL語句；
4. 最后備份數(shù)據(jù)庫的日志，對文件組的還原是需要通過日志備份才能還原的；
在舊數(shù)據(jù)庫執(zhí)行上面的SQL腳本，將會產(chǎn)生生成下面的SQL（只保留了部分SQL）：
--設(shè)置完整模式USE [master]GOALTER DATABASE [DataBaseName] SET RECOVERY FULL WITH NO_WAITGO--備份分區(qū)- FG_Archive_Id_01BACKUP DATABASE [DataBaseName]FILEGROUP = 'FG_Archive_Id_01'TO DISK = 'D:\DBBackup\FG_Archive_Id_01.bak' WITH FORMATGO--備份分區(qū)- FG_Archive_Id_02BACKUP DATABASE [DataBaseName]FILEGROUP = 'FG_Archive_Id_02'TO DISK = 'D:\DBBackup\FG_Archive_Id_02.bak' WITH FORMATGO--備份分區(qū)- FG_Archive_IndexBACKUP DATABASE [DataBaseName]FILEGROUP = 'FG_Archive_Index'TO DISK = 'D:\DBBackup\FG_Archive_Index.bak' WITH FORMATGO--備份分區(qū)- PRIMARYBACKUP DATABASE [DataBaseName]FILEGROUP = 'PRIMARY'TO DISK = 'D:\DBBackup\PRIMARY.bak' WITH FORMATGO--備份日志BACKUP LOG [DataBaseName]TO DISK = 'D:\DBBackup\Barefoot.Archives_Log.bak' WITH FORMATGO
執(zhí)行完上面的腳本，會生成下圖所示的備份文件：
F4_備份文件列表
(二) 下面是用于生成還原SQL的代碼，這個代碼需要提供幾個變量值：
1. @DataBaseName指定需要進行備份的數(shù)據(jù)庫名，值為'Barefoot.Archives'；
2. @BackupPath在新服務(wù)器文件組備份的地址，值為：'E:\DBBackup\'；
3. @SavePath_Drive存在數(shù)據(jù)文件的盤符，值為：'F:\'；
4. @SavePath_FolderName存放數(shù)據(jù)文件的文件夾，值為：'DataBase\'；
5. @SavePath_SubFolderName存放ndf文件的文件夾，值為：'FG_Archive\'；
6. @IsSamePath表示是否延續(xù)之前的physical_name值，值為1表示延續(xù)，這樣會使用@SavePath_Drive替換physical_name的盤符，這樣@SavePath_FolderName和@SavePath_SubFolderName就不會起作用了，值為0表示不延續(xù)，這樣physical_name的值=@SavePath_Drive+@SavePath_FolderName+@SavePath_SubFolderName；
在舊數(shù)據(jù)庫Barefoot.Archives中執(zhí)行下面的SQL腳本：
-- ====================-- Author: <聽風(fēng)吹雨>-- Blog: -- Create date: <2014/02/26>-- Description: <生成分區(qū)還原腳本>-- =======================DECLARE @DataBaseName SYSNAME--數(shù)據(jù)庫名稱DECLARE @BackupPath SYSNAME--保存?zhèn)浞菸募穆窂紻ECLARE @SavePath_Drive SYSNAME--保存數(shù)據(jù)庫文件的盤符DECLARE @SavePath_FolderName SYSNAME--保存數(shù)據(jù)庫的文件夾DECLARE @SavePath_SubFolderName SYSNAME--保存分區(qū)的文件夾DECLARE @FilegroupName SYSNAME--分區(qū)文件組名稱DECLARE @FileName SYSNAME--分區(qū)文件名稱DECLARE @PhysicalName SYSNAME--物理路徑DECLARE @IsSamePath INT--是否跟遠路徑一樣1,0DECLARE @sql NVARCHAR(MAX)--sql字符串--設(shè)置下面變量SET @DataBaseName = 'DataBaseName'SET @BackupPath = 'E:\DBBackup\'SET @SavePath_Drive = 'F:\'SET @SavePath_FolderName = 'DataBase\'SET @SavePath_SubFolderName = 'FG_Archive\'SET @IsSamePath = 1--1.還原主分區(qū)SELECT @FilegroupName = [name] FROM sys.filegroups WHERE is_default = 1PRINT '--還原主分區(qū)'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 = 10GO'PRINT @sql + CHAR(13)--2.還原分區(qū)DECLARE @itemCur CURSORSET @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 = 0OPEN @itemCurFETCH NEXT FROM @itemCur INTO @FileName,@PhysicalName,@FilegroupNameWHILE @@FETCH_STATUS=0BEGIN --邏輯處理 PRINT '--還原分區(qū)- ' + @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 + ''',NORECOVERYGO' PRINT @sql + CHAR(13) FETCH NEXT FROM @itemCur INTO @FileName,@PhysicalName,@FilegroupNameEND CLOSE @itemCurDEALLOCATE @itemCur--3.還原日志PRINT '--還原日志'SET @sql = 'RESTORE LOG [' + @DataBaseName + ']FROM DISK = ''' + @BackupPath + @DataBaseName + '_Log.bak''WITH NORECOVERYGO'PRINT @sql + CHAR(13)--4.還原在線PRINT '--還原在線'SET @sql = 'RESTORE DATABASE [' + @DataBaseName + ']WITH RECOVERYGO'PRINT @sql + CHAR(13)
上面SQL腳本的邏輯是：
1. 通過系統(tǒng)表sys.filegroups找到默認文件組，先還原這個主文件；
2. 使用游標(biāo)的方式來循環(huán)系統(tǒng)表sys.filegroups，拿到文件組名稱，生成文件組對應(yīng)的還原SQL語句；
3. 接著還原數(shù)據(jù)庫的日志；
4. 最后還原在線，讓數(shù)據(jù)庫在線；
執(zhí)行上面的SQL腳本，將會產(chǎn)生生成下面的SQL（只保留了部分SQL）：
--還原主分區(qū)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 = 10GO--還原分區(qū)- FG_Archive_Id_01RESTORE 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',NORECOVERYGO--還原分區(qū)- FG_Archive_Id_02RESTORE 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',NORECOVERYGO--還原分區(qū)- FG_Archive_IndexRESTORE 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',NORECOVERYGO--還原日志RESTORE LOG [DataBaseName]FROM DISK = 'E:\DBBackup\Barefoot.Archives_Log.bak'WITH NORECOVERYGO--還原在線RESTORE DATABASE [DataBaseName]WITH RECOVERYGO
在新服務(wù)器上執(zhí)行上面的SQL腳本還原數(shù)據(jù)庫，需要注意的是：在還原在線之前數(shù)據(jù)庫都是一直處于：正在還原的狀態(tài)的；
五.注意事項（Attention）
1. 在實際運用中，可以結(jié)合本文和SQL Server 數(shù)據(jù)庫遷移偏方進行靈活結(jié)合運用，當(dāng)通過本文件組備份后，舊庫繼續(xù)進數(shù)據(jù)，在花銷時間最大的網(wǎng)絡(luò)傳輸過程和還原過程繼續(xù)對老庫進數(shù)據(jù)，這樣當(dāng)還原好數(shù)據(jù)庫之后使用SQL Server 數(shù)據(jù)庫遷移偏方來轉(zhuǎn)移最新的數(shù)據(jù)，這樣宕機的時間會趨向于0；
2. 其實為了確保某些文件組不被修改，可以設(shè)置文件組的只讀屬性，這樣可以確保只有某個文件組在進新數(shù)據(jù)，可惜的是設(shè)置了只讀也無法拷貝這些文件組文件通過FTP傳輸，提示：操作無法完成，因為文件已在SQL Server（MSSQLSERVER）中打開。
3. 上面腳本的每個文件組中只包含了一個文件，如果一個文件組包含多個文件，那就需要修改下腳本了；
4. 高文佳曾經(jīng)說過，可以先刪除索引，再壓縮備份，還原之后再創(chuàng)建索引，是的，這不防是一個好方法，不過需要考慮兩點，一個是在還原之后創(chuàng)建索引的速度與時間，如果磁盤速度不算快，那你就要考慮刪除索引是否適合了；另外一點是你的數(shù)據(jù)庫是否能停機讓你刪除索引，這個跟具體的業(yè)務(wù)有關(guān)；
六.疑問（Questions）
1. 對primary進行完整文件組備份（作為基備份），對FG1進行完整文件組備份（作為基備份）這些描述有問題吧？對primary進行完整文件組備份應(yīng)該不會生成基線的吧？ SQL文件組備份和還原
2. 如果在同一個文件組中有兩個以上的分區(qū)值，就是把兩個段的分區(qū)方案中同指向同一個分區(qū)文件組，那在備份和還原有什么需要注意的呢？能成功備份還原嘛？
--備份分區(qū)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 FORMATGO--還原分區(qū)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', NORECOVERYGO
解答：從備份和還原的代碼可以看出只是把FILEGROUP與bak對應(yīng)，與ndf文件對應(yīng)，所以是不需要理會這個文件組中包含了多少個邏輯分區(qū)；
