SQL 2008 Recovery 4 - filegroup restore

--file restore sample, page 3-35

--須以 SQLCMD mode 執行這個範例
USE [master]
GO

--DROP DATABASE Nwind

!!md C:\myAdmin\DB
!!md C:\myAdmin\Backup
--因為要做 File 的備份/還原，所以需要建立多個 File
--先建立一個具有多個filegroup的DB,
CREATE DATABASE [Nwind] ON PRIMARY( NAME=N'Nwind_P',FILENAME = N'C:\myAdmin\DB\Nwind_P.mdf' ),
FILEGROUP GroupA( NAME=N'Nwind_A',FILENAME = N'C:\myAdmin\DB\Nwind_A.ndf' ),
FILEGROUP GroupB( NAME=N'Nwind_B',FILENAME = N'C:\myAdmin\DB\Nwind_B.ndf' ),
FILEGROUP GroupC( NAME=N'Nwind_C',FILENAME = N'C:\myAdmin\DB\Nwind_C.ndf' )
GO

USE Nwind
GO
--在各檔案群組各放一個資料表
--不同的table放在不同的filegroup上
CREATE TABLE tbP(c1 INT,c2 nvarchar(10)) ON [PRIMARY]
CREATE TABLE tbA(c1 INT,c2 nvarchar(10)) ON [GroupA]
CREATE TABLE tbB(c1 INT,c2 nvarchar(10)) ON [GroupB]
CREATE TABLE tbC(c1 INT,c2 nvarchar(10)) ON [GroupC]
GO

-- 各資料表所屬的 Filegroup
-- 以下指令可以看 資料表位在哪個檔案群組上
SELECT objs.schemaN N'結構描述', objs.tableN N'資料表', das.name N'檔案群組'
FROM Nwind.sys.data_spaces das INNER JOIN
(
SELECT DISTINCT object_id,data_space_id
FROM Nwind.sys.indexes
WHERE type BETWEEN 0 AND 1
) idx
ON das.data_space_id = idx.data_space_id
INNER JOIN
(
SELECT sys.objects.name 'tableN', object_id, sys.schemas.name 'schemaN'
FROM Nwind.sys.objects INNER JOIN Nwind.sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type='U'
) objs
ON objs.object_id = idx.object_id
ORDER BY 3

-- 分析各個資料檔案的屬性
-- 各資料檔案的state_desc 都是 ONLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

-- 分析資料

SELECT * FROM Nwind.dbo.tbP
UNION ALL
SELECT * FROM Nwind.dbo.tbA
UNION ALL
SELECT * FROM Nwind.dbo.tbB
UNION ALL
SELECT * FROM Nwind.dbo.tbC

-- INSERT DATA
-- 資料都存在
INSERT Nwind.dbo.tbP VALUES(1,'P')
INSERT Nwind.dbo.tbA VALUES(1,'A')
INSERT Nwind.dbo.tbB VALUES(1,'B')
INSERT Nwind.dbo.tbC VALUES(1,'C')

-- 執行DB
BACKUP DATABASE Nwind TO DISK=N'C:\myAdmin\Backup\Nwind_PiecemealBackup.bak' WITH INIT

-- 使用 SQLCMD 模式
!!net stop mssqlserver /Y
--exec master.dbo.xp_cmdshell 'net stop mssqlserver /Y'

-- 破壞 C:\myAdmin\DB\Nwind_B.ndf
-- DEL是防呆 本來就應該不存在
!!Del C:\myAdmin\DB\Nwind_B.bad
!!ren "C:\myAdmin\DB\Nwind_B.ndf" *.BAD

!!net start mssqlserver /Y

/*
訊息 945，層級 14，狀態 2，行 1
檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'Nwind'。詳細資訊請參閱 SQL Server 錯誤記錄檔。
*/
-- 檢查 SQL Server 記錄檔
/*
2008-09-07 12:02:46.41 spid30s 錯誤: 17207，嚴重性: 16，狀態: 1。
2008-09-07 12:02:46.41 spid30s FileMgr::StartSecondaryDataFiles: Operating system error 2(系統找不到指定的檔案。) occurred while creating or opening file 'C:\myAdmin\DB\Nwind_B.ndf'. Diagnose and correct the operating system error, and retry the operation.
2008-09-07 12:02:46.41 spid30s 錯誤: 5120，嚴重性: 16，狀態: 5。
2008-09-07 12:02:46.41 spid30s 無法開啟實體檔案 "C:\myAdmin\DB\Nwind_B.ndf"。作業系統錯誤 2: "2(系統找不到指定的檔案。)"。
*/
-- 需要重新連接回 SQL Server
-- 分析資料，Error
-- 因為 NWind_B 毀損, 無法正常使用整個DB
SELECT * FROM Nwind.dbo.tbP

-- 檢查資料庫的狀態
-- Nwind 在 RECOVERY_PENDING 狀態
SELECT name, state_desc
FROM sys.databases

-- OFFLINE
-- 把 Nwind_B 設定成offline,這樣其他的filegroup才能正常被使用
ALTER DATABASE Nwind
MODIFY FILE
(name = N'Nwind_B', OFFLINE)
go

-- Database are Online
-- 再把 Nwind online回來(從recovery_pending改回online)
ALTER DATABASE Nwind
SET ONLINE
go

-- 分析各個資料檔案的屬性 -- OFFLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

-- 分析資料，僅有 tbB 錯誤
SELECT * FROM Nwind.dbo.tbP
GO
SELECT * FROM Nwind.dbo.tbA
GO
SELECT * FROM Nwind.dbo.tbB -- 錯誤
GO
SELECT * FROM Nwind.dbo.tbC
GO

INSERT Nwind.dbo.tbP VALUES(2,'P')
GO
INSERT Nwind.dbo.tbA VALUES(2,'A')
GO
INSERT Nwind.dbo.tbB VALUES(2,'B') --錯誤
GO
INSERT Nwind.dbo.tbC VALUES(2,'C')

/*
訊息 8653，層級 16，狀態 1，行 1
查詢處理器無法產生資料表或檢視 'tbB' 的計劃，因為資料表存在於非線上的檔案群組中。
*/

-- Backup LOG --> tail-log backup
/*
記得先將 Nwind_PiecemealBackup.bak，存放到 C:\myAdmin\Backup 中。
*/
-- 注意,這邊只有 NO_TRUNCATE, 並沒有 NO_RECOVERY
-- (因為我們要讓其他table在我們修復Nwind_B的時候仍能使用, online recovery)
BACKUP LOG Nwind
TO DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH NAME = 'LOG_backupset',
NO_TRUNCATE

-- 以下三個指令都只是查看資訊
--
RESTORE HEADERONLY
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'

-- 這個指令可以看某個bak file裡面,到底是哪些filegroup的備份
RESTORE FILELISTONLY
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH FILE=1

-- FamilyCount 表示,只有一個備份檔案
-- FamilySequenceNumber 表示這是檔案組中的第一個
RESTORE labelONLY
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'



-- RESTORE DATABASE -- File
-- 將Nwind_B 備份回來
RESTORE DATABASE Nwind
FILE = N'Nwind_B'
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH RECOVERY

/*
已處理資料庫 'Nwind' 的 16 頁，檔案 1 上的檔案 'Nwind_B'。
向前復原目前的起始點位於記錄序號 (LSN) 21000000032100001。
必須有超過 LSN 21000000038200001 的其他向前復原，才能完成還原順序。
RESTORE DATABASE ... FILE= 已於 0.048 秒內成功處理了 16 頁 (2.604 MB/sec)。
*/

-- 分析各個資料檔案的屬性 -- RESTORING
-- 此時Nwind_B 的 state_desc 變成 RESTORING
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

--restore tail log
-- file = 2 表示是 右鍵這個backup device->media contents-> position 數字為2的
RESTORE LOG Nwind
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH FILE = 2,
RECOVERY

-- 分析各個資料檔案的屬性 -- ONLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

-- 分析資料，可以完整出來所有的資料內容
SELECT * FROM Nwind.dbo.tbP
UNION ALL
SELECT * FROM Nwind.dbo.tbA
UNION ALL
SELECT * FROM Nwind.dbo.tbB
UNION ALL
SELECT * FROM Nwind.dbo.tbC