[MSSQL 2008] File / File Group還原
------------------------
-- 建立測試DB,除Primary Group之外,另新增二個FileGroup - FG_A及FG_B
------------------------
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N'File_P', FileNAME = N'C:\MSSQL_Data\TestDB_P.mdf'),
FileGROUP [FG_A]
( NAME = N'File_A', FileNAME = N'C:\MSSQL_Data\TestDB_A.ndf'),
FileGROUP [FG_B]
( NAME = N'File_B', FileNAME = N'C:\MSSQL_Data\TestDB_B.ndf')
LOG ON
( NAME = N'TestDB_log', FileNAME = N'C:\MSSQL_Data\TestDB_log.ldf')
GO
------------------------
-- 建立測試Table,分別存放於不同FileGroup
------------------------
Use TestDB
GO
CREATE TABLE TB_P (P_column char(10) NULL )
CREATE TABLE TB_A (A_column char(10) NULL ) ON FG_A
CREATE TABLE TB_B (B_column char(10) NULL ) ON FG_B
GO
------------------------
-- 查看Table所屬的FileGroup
------------------------
Select g.name as GroupName, f.TableName
From sys.data_spaces g
Left JOIN
(
Select o.object_id, o.name as TableName, i.data_space_id
From sys.objects o INNER JOIN sys.indexes i
On o.type = 'U' And (i.type = 0 or i.type = 1) And o.object_id = i.object_id
)f
ON
g.data_space_id = f.data_space_id
GO
------------------------
-- 新增測試資料
------------------------
Insert into TB_P values('PPPPP'),('PPPPP')
Insert into TB_A values('AAAAA'),('AAAAA')
Insert into TB_B values('BBBBB'),('BBBBB')
GO
------------------------
-- 建立資料庫備份
------------------------
/* 完整備份 */
Backup Database TestDB
To Disk= 'C:\MSSQL_Data\Backup\TestDB.bak'
GO
/* Partial備份 */
Backup Database TestDB
Filegroup = 'Primary'
To disk= 'C:\MSSQL_Data\Backup\TestDB_P.bak'
GO
Backup Database TestDB
Filegroup = 'FG_A'
To disk= 'C:\MSSQL_Data\Backup\TestDB_A.bak'
GO
Backup Database TestDB
Filegroup = 'FG_B'
To disk= 'C:\MSSQL_Data\Backup\TestDB_B.bak'
GO
/*第一次 Log備份 */
Backup log TestDB
To Disk= 'C:\MSSQL_Data\Backup\TestDB_L.bak'
GO
------------------------
-- 新增測試資料
------------------------
Insert into TB_P values('PPPPP2'),('PPPPP2')
Insert into TB_A values('AAAAA2'),('AAAAA2')
Insert into TB_B values('BBBBB2'),('BBBBB2')
GO
------------------------
-- 第二次Log備份
------------------------
Backup log TestDB
To Disk= 'C:\MSSQL_Data\Backup\TestDB_L2.bak'
GO
------------------------
-- 查看備份檔裡的file
------------------------
Restore Filelistonly
From Disk= 'C:\MSSQL_Data\Backup\TestDB.bak'
GO
------------------------
-- 1. 從完整備份還原檔案
------------------------
/* 1.1 只還原Primary Group裡的檔案 */
Restore database TestDB_Restore
File = 'File_P'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore_P.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\TestDB_Restore_A.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\TestDB_Restore_B.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF'
GO
-- 僅TB_P是on line,其它2個table無法查詢。
Select * From TestDB_Restore .dbo.TB_P -- ok
Select * From TestDB_Restore .dbo.TB_A -- error
Select * From TestDB_Restore .dbo.TB_B -- error
GO
--未使用Norecovery,將無法再還原Log檔
RESTORE LOG TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L.bak'
/* 1.2 還原其它群組的檔案 */
Restore database TestDB_Restore
File = 'File_P', -- Primary Group裡的檔案也需還原,才能還原其它群組的檔案
File = 'File_A'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace --若TestDB_Restore已存在則取代
--可再還原其它群組,但未加Norecovery故無法再還原Log檔
GO
-- 或 Restore With Recovery (無法再還原Log檔)
-- 先還原Primary Group裡的檔案
Restore database TestDB_Restore
File = 'File_P'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace, Recovery
GO
-- 再還原其它檔案群組裡的檔案(
Restore database TestDB_Restore
File = 'File_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
GO
-- 或 Restore With Norecovery
Restore database TestDB_Restore
File = 'File_P'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace, Norecovery
-- with Norecovery需將所有檔案還原後,DB才可正常服務。或with Partial只還原部份檔案
GO
Restore database TestDB_Restore
File = 'File_A'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With Norecovery
GO
Restore database TestDB_Restore
File = 'File_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With Norecovery
GO
RESTORE LOG TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L.bak'
with Norecovery
GO
RESTORE LOG TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L2.bak'
GO
------------------------
-- 2. 從完整備份檔還原檔案群組
------------------------
/* 2.1 只還原Primary Group */
Restore database TestDB_Restore
Filegroup = 'PRIMARY'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace
GO
/* 2.2 還原其它檔案群組 */
Restore database TestDB_Restore
Filegroup = 'PRIMARY', -- Primary Group裡的檔案也需還原,才能還原其它群組的檔案
Filegroup = 'FG_A'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace
GO
-- 或 Restore With Recovery
-- 先還原Primary Group
Restore database TestDB_Restore
File = 'File_P'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace, Recovery
GO
-- 再還原其它檔案群組
Restore database TestDB_Restore
File = 'File_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
GO
-- 或 Restore With Norecovery
Restore database TestDB_Restore
Filegroup = 'PRIMARY'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace, Norecovery, Partial -- with Partial可以只還原部份檔案
GO
Restore database TestDB_Restore
Filegroup = 'FG_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
GO
------------------------
-- 3. 從Partial備份還原檔案
------------------------
/* 3.1 只還原Primary Group裡的檔案 */
Restore database TestDB_Restore
File = 'File_P'
From disk = 'C:\MSSQL_Data\Backup\TestDB_P.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace
GO
/* 3.2 還原其它檔案群組 */
Restore database TestDB_Restore
File = 'File_P'
From disk = 'C:\MSSQL_Data\Backup\TestDB_P.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace, Norecovery, Partial
-- with Norecovery需將所有檔案還原後,DB才可正常服務。或with Partial只還原部份檔案)
GO
Restore LOG TestDB_Restore
From disk = 'C:\MSSQL_Data\Backup\TestDB_L.bak'
GO
------------------------
-- 4. 從Partial備份還原檔案群組
------------------------
/* 4.1 只還原Primary Group */
Restore database TestDB_Restore
Filegroup = 'PRIMARY'
From disk = 'C:\MSSQL_Data\Backup\TestDB_P.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace
GO
/* 4.2 還原其它檔案群組 */
Restore database TestDB_Restore
Filegroup = 'PRIMARY'
From disk = 'C:\MSSQL_Data\Backup\TestDB_P.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace, Norecovery
-- with Norecovery需將所有檔案還原後,DB才可正常服務。或with Partial只還原部份檔案)
GO
Restore database TestDB_Restore
Filegroup = 'FG_A'
From disk = 'C:\MSSQL_Data\Backup\TestDB_A.bak'
GO
Restore database TestDB_Restore
Filegroup = 'FG_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB_B.bak'
GO
Restore LOG TestDB_Restore
From disk = 'C:\MSSQL_Data\Backup\TestDB_L.bak'
GO
------------------------
-- 5. 還原至第二次Log備份
------------------------
Restore database TestDB_Restore
Filegroup = 'PRIMARY'
From disk = 'C:\MSSQL_Data\Backup\TestDB_P.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\FG_A_Restore.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\FG_B_Restore.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Replace,
Norecovery, -- 要還原Log檔需Norecovery,
Partial -- 若沒Partial則需每個群組都還原後DB才可正常服務
GO
Restore log TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L.bak'
WITH NORECOVERY
GO
Restore log TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L2.bak'
GO
Select * From TestDB_Restore.dbo.TB_P
Select * From TestDB_Restore.dbo.TB_A --error
GO
Restore database TestDB_Restore
Filegroup = 'FG_A'
From disk = 'C:\MSSQL_Data\Backup\TestDB_A.bak'
GO
Restore database TestDB_Restore
Filegroup = 'FG_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB_B.bak'
GO
Restore LOG TestDB_Restore
From disk = 'C:\MSSQL_Data\Backup\TestDB_L.bak'
GO
Restore log TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L2.bak'
Select * From TestDB_Restore.dbo.TB_A –OK
-- 或
Restore database TestDB_Restore
File = 'File_P',
File = 'File_A',
File = 'File_B'
From disk = 'C:\MSSQL_Data\Backup\TestDB.bak'
With
Move 'File_P' To 'C:\MSSQL_Data\Restore\TestDB_Restore_P.mdf',
Move 'File_A' To 'C:\MSSQL_Data\Restore\TestDB_Restore_A.ndf',
Move 'File_B' To 'C:\MSSQL_Data\Restore\TestDB_Restore_B.ndf',
Move 'TestDB_log' To 'C:\MSSQL_Data\Restore\TestDB_Restore_log.LDF',
Norecovery, Replace
GO
RESTORE LOG TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L.bak'
with norecovery
GO
RESTORE LOG TestDB_Restore
FROM Disk= 'C:\MSSQL_Data\Backup\TestDB_L2.bak'
GO
------------------------
-- 查看各群組裡的Table資料
------------------------
Select * From TestDB_Restore.dbo.TB_P
Select * From TestDB_Restore.dbo.TB_A
Select * From TestDB_Restore.dbo.TB_B
GO
------------------------
-- 刪除測試DB
------------------------
Use Master
GO
Drop Database TestDB
GO
Drop Database TestDB_Restore
GO
-- 連續執行本範例程式Restore DB時,由於每一次Restore的群組不一定相同,可能出現以下警訊
建議每次Restore時先Drop database TestDB_Restore