[MSSQL 2008] File / File Group還原

[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'

GO
clip_image002[4]

/* 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的群組不一定相同,可能出現以下警訊
image

  建議每次Restore時先Drop database TestDB_Restore