[SQL SERVER][Maintain]檔案群組備份和還原
前言
在實務上一般正式環境資料庫應該都上百GB，
執行一次完整備份相當花費時間及資源(大部分都花費在大的資料表和索引)，
為了縮短備份時間，我大多會利用檔案群組管理這些大資料表和索引，
習慣上我會再多切兩個檔案群組來存放這些大資料表和大索引，不管是在管理或復原也都相當方便且彈性，
當然在效能方面也是有不錯的表現(每一檔案群組對應實體硬碟也要不同)，
而檔案群組也可以結合差異及交易紀錄備份，當資料庫部分資料誤刪或毀損時，
可以在不復原資料庫主要檔案群組下，只復原部分資料庫檔案物件，
而且資料庫能也能正常線上使用，下面我就來實作，利用檔案群組復原資料庫物件。
實作檔案群組備份和還原
use master
go
create database ricotest on primary
(name='ricotest',filename='E:\sqldata\ricotest.mdf',size=100mb,maxsize=300mb,filegrowth=50mb),
--存放大資料表的檔案群組
filegroup fg_BigTbl
(name='ricotest_BigTbl',filename='E:\sqldata\ricotest_BigTbl.ndf',size=100mb,maxsize=300mb,filegrowth=50mb),
--存放大索引的檔案群組
filegroup fg_BigIdx
(name='ricotest_BigIdx',filename='E:sqldata\ricotest_BigIdx.ndf',size=100mb,maxsize=300mb,filegrowth=50mb)
log on
(name='ricotest_log',filename='E:\sqlldf\ricotest_log.ldf',size=20mb,maxsize=100mb,filegrowth=10mb)
go
--復原模式=完整
alter database ricotest set recovery full
use ricotest
--存放在預設群組(primary)
create table tblA
(
c1 int identity(1,1),
c2 varchar(10),
c3 datetime
) on [primary]
create clustered index cidx_c1
on tblA(c1)
on [primary]
--存放在大資料表群組(fg_BigTbl)
create table tblB
(
c1 int identity(1,1),
c2 varchar(10),
c3 datetime
) on [fg_BigTbl]
--存放在大索引群組(fg_BigIdx)
create nonclustered index nidx_c2
on tblB(c2)
on [fg_BigIdx]
--存放在大資料表群組(fg_BigTbl)
create table tblC
(
c1 int identity(1,1),
c2 varchar(10),
c3 datetime
) on [fg_BigTbl]
--確認相關物件所屬的檔案群組
SELECT t.name as [資料表名稱] ,o.[type] as [物件類型], i.[name] as [索引名稱], f.name as [檔案群組名稱]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
inner join sys.tables t
on t.object_id=o.object_id
and o.type='U'
GO
--確認檔案狀態
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM sys.database_files
--新增資料
insert into tblA
select 'A',GETDATE() union all
select 'A',GETDATE() union all
select 'A',GETDATE()
insert into tblB
select 'B',GETDATE() union all
select 'B',GETDATE() union all
select 'B',GETDATE()
insert into tblC
select 'C',GETDATE() union all
select 'C',GETDATE() union all
select 'C',GETDATE()
備份資料庫
--完整備份
backup database ricotest
to disk='e:\ricotest_full.bak'
with compression
--第一次交易備份
backup log ricotest
to disk='e:\ricotest_1.trn'
with compression
--備份fg_BigTbl
backup database ricotest
filegroup ='fg_BigTbl'
to disk='E:\fg_BigTbl.bak'
with compression
--備份fg_BigIdx
backup database ricotest
filegroup ='fg_BigIdx'
to disk='E:\fg_BigIdx.bak'
with compression
破壞資料庫(這裡我刪除ricotest_BigIdx.ndf檔案)並開始還原
嘗試將資料庫改為線上，便會收到以下錯誤
alter database ricotest
set online
確認資料庫狀態
select name,state,state_desc
from sys.databases
where name='ricotest'
錯誤訊息很明顯是某個檔案不存在，而該檔案室是在fg_BigIdx檔案群組，
所以我們利用該群組備份來還原，而不需拿完整備份來還原。
--先結尾交易紀錄
backup log ricotest
to disk='e:\tail_log.trn'
with norecovery,compression
--準備從fg_BigIdx.bak還原
restore database ricotest
file='ricotest_BigIdx'
from disk='e:\fg_BigIdx.bak'
with norecovery
restore database ricotest
from disk='e:\tail_log.trn'
with recovery
確認資料
假如資料庫只有使用單一檔案群組，如果不幸有天某個物件或頁面損壞，
這將會導致整個資料庫無法使用(老師都有教，雞蛋不可以全部都放在同一籃..XD)，
這時你就會覺得檔案群組是多麼的好用，下面我來實作整個過程。
無法開啟資料庫
查看ErrorLog可以知道ricoteset_BigTbl.ndf檔案不存在，
導致整個資料庫無法使用，所以我們先修改該檔案為離線，並啟用資料庫。
--離線ricotest_BigTbl檔案群組
alter database ricotest
modify file
(name='ricotest_BigTbl',offline)
--啟用資料庫
alter database ricotest
set online
--確認資料庫狀態
select d.state_desc from sys.databases d where d.name='ricotest'
確認資料
可以看到無法查看tblB和tblC相關資料，但主要的資料表(tblA)是沒有問題的，
所以影響使用者程度不至於太大，後面就按照上面的方法復原ricotest_BigTbl檔案群組，
相關資料表就可以查詢了。
參考
