資料庫的piecemeal回復(資料群組)

by adonis 28. 十月 2013 17:02

前言

在上集中,我們提到了 SQL Server的檔案型 piecemeal回復功能,可以幫助各位知道目前回復資料庫的進度以方便了解還要多久資料庫才會回復完成,而本期將要介紹的是檔案群組型的 piecemeal回復功能,使用部分備份以及還原的機制適用更大型的資料庫使用。

使用檔案群組類型的 piecemeal 回復資料庫

通常使用這一類型的資料庫回復是因為資料庫太過龐大無法使用一般的標準備份還原方式(或是稱為完整資料備份方式),必需要採用「部分」備份方能滿足我們的需求。
換句話說,當資料庫大到一定的程度時,你又分了很多檔案群組,如果還是完整備份資料的話,勢必會浪費很多的時間。
檔案群組的 piecemeal回復,最大的重點在於備份與還原都不一定要使用完整資料庫備份,可以採用部分備份的方式,這是其他方式的備份還原所沒有的。
環境準備
為了能夠方便做練習,我們需要先準備資料庫的建立,產生資料庫如下:

CREATE DATABASE [AdventureWorksTest] ON  PRIMARY
( NAME = N'AdventureWorksTest', FILENAME = N'C:\TSQLDB\AdventureWorksTest.mdf' , SIZE = 5072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [DYNAMIC_DATA]
( NAME = N'AW_DynamicData', FILENAME = N'C:\TSQLDB\AW_DynamicData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [STATIC_DATA]
( NAME = N'AW_StaticData', FILENAME = N'C:\TSQLDB\AW_StaticData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'AdventureWorksTest_log', FILENAME = N'C:\TSQLDB\AdventureWorksTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

先建立一個資料庫 AdventureWorksTest,有三個檔案群組,分別是 Primary、Dyncmic_Dada以及 Static_Data三個,並且之後會將 Static_Data檔案群組設定成唯讀。

接下來,建立兩個資料表:

CREATE TABLE [dbo].[State]
(
[State] [nchar](2) NOT NULL,
CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
(   
[State] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [STATIC_DATA]
) ON [STATIC_DATA]
END

GO

INSERT [dbo].[State] ([State]) VALUES (N'IL')
INSERT [dbo].[State] ([State]) VALUES (N'WA')
GO
CREATE TABLE [dbo].[Customer](
    [CustomerID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [State] [nchar](2) NULL,

CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
    [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DYNAMIC_DATA]
) ON [DYNAMIC_DATA]
END
GO
INSERT [dbo].[Customer] ([CustomerID], [FirstName], [LastName], [City], [State]) VALUES (N'92560639-5eb2-4719-9d33-0428f496d2fc', N'Billy D.', N'Williams', N'Chicago', N'IL')
INSERT [dbo].[Customer] ([CustomerID], [FirstName], [LastName], [City], [State]) VALUES (N'cef4f5e8-9a7a-4f15-b382-29002fc302ed', N'Bill', N'Gates', N'Seattle', N'WA')

這兩個資料表,一個是屬於 Dynamic_Data檔案群組,另一個則是 Static_Data檔案群組,之後不要忘記要把 Static_Data這個檔案群組設定成唯讀。

開始進行備份,首先,先做 read_write_filegroups檔案群組的備份,如下:

backup database AdventureworksTest read_write_filegroups to disk ='c:\AWBackups\AdventureWorksTest_Dynamic_Data.bak'

我們並沒有進行完整資料庫備份而是做所謂的「部分」備份,read_write_filegroups就是只備份讀寫的檔案群組,所以非常特殊,所以我們故意把 Static_Data這個檔案群組設定成唯讀就是這個意思,在此我們只備份了 Primary以及 Dynamic_Data這兩個檔案群組。

backup database AdventureworksTest filegroup='static_data' to disk ='c:\AWBackups\AdventureWorksTest_Static_Data.bak'

接下來,只備份 static_data檔案群組,所以這兩次備份可能是分開做的,這樣就不需要使用完整資料庫備份。

接下來,我們要把 SQL Server的服務停止,並且破壞 ndf的檔案,在此注意不能破壞 mdf的檔案,因為如果本次的設定破壞的 primary的檔案群組,就無法使用 piecemeal回復了,所以務必只能破壞非 primary的檔案群組(以本例而言是 ndf檔)。

重新啟動 SQL Server服務後,你會發現資料庫是無法使用的,這時你必需先進行 tail-log的備份:
backup log AdventureWorksTest to disk ='c:\AWBackups\AdventureWorksTest_Active_Log.bak' with no_truncate,norecovery

這個 tail-log檔案可以給多次檔案群組的 piecemeal回復使用,而不是像上一期檔案類型的piecemeal回復一樣,每次都要再備份一次 tail-log才行(這也是我懷疑為何檔案型的 piecemeal要重複備份 tail-log,實在讓人不解)。

接下來,重點的部分來了,如果要使用檔案群組的 piecemeal restore,備份完 tail-log之後,第一次回復檔案群組時需要加關鍵字 partial,如下:
restore database AdventureWorksTest filegroup='dynamic_data' from disk ='c:\AWBackups\AdventureWorksTest_Dynamic_Data.bak' with partial,norecovery

這時也宣告 piecemeal restore的開始,然後把 tail-log 回復,如下:

restore log AdventureWorksTest from disk ='c:\AWBackups\AdventureWorksTest_Active_log.bak' with recovery

這時你的資料庫就可以看了,但是壞掉的檔案群組依舊無法使用,不過就和前期所提到的檔案 piecemeal回復一樣,你可以一個個把檔案群組回復回來,這樣資料就一個個可以看見了。

不過,因為我們設定的 static_data這個檔案群組是唯讀的,所以我們只需要回復檔案群組即可,不需要再把 tail-log附加回去:

restore database AdventureWorksTest filegroup='STATIC_DATA' from disk='c:\AWBackups\AdventureWorksTest_Static_Data.bak' with recovery

整個大功告成,所以順序就是:

1.    Tail-log備份
2.    回復 A檔案群組(使用 with partial關鍵字)
3.    回復 1的 tail-log
4.    回復 B檔案群組
5.    回復 1的 tail-log。

就這樣無限循環下去~~~

這就是檔案群組的 piecemeal的資料庫回復。

Tags:

評論 (49) -

cours de theatre
cours de theatre United States
2017/10/1 上午 12:16:02 #

I really liked your blog article.Thanks Again. Really Great.

回覆

dic phoenix
dic phoenix United States
2017/10/7 上午 01:15:45 #

Thanks for sharing, this is a fantastic blog. Much obliged.

回覆

Osimi seaview
Osimi seaview United States
2017/10/9 下午 07:03:37 #

Muchos Gracias for your blog post. Much obliged.

回覆

Buy illegal backlinks
Buy illegal backlinks United States
2017/10/12 下午 09:42:45 #

I really liked your blog post. Want more.

回覆

dragon city hack mod
dragon city hack mod United States
2017/10/15 下午 04:29:08 #

I really enjoy the article post.Much thanks again. Keep writing.

回覆

why not check here
why not check here United States
2017/10/17 下午 03:46:49 #

Hey, thanks for the article.Really looking forward to read more. Really Great.

回覆

sletrokor review
sletrokor review United States
2017/10/17 下午 09:18:39 #

Major thankies for the article post.Really thank you! Great.

回覆

VigRx Plus
VigRx Plus United States
2017/10/19 上午 08:23:09 #

Very good post.Really thank you! Cool.

回覆

check over here
check over here United States
2017/10/19 下午 07:24:05 #

Thanks again for the article post.Much thanks again.

回覆

can ho son thinh
can ho son thinh United States
2017/10/21 上午 04:41:11 #

I truly appreciate this blog post.Really looking forward to read more.

回覆

can ho vung tau
can ho vung tau United States
2017/10/28 下午 01:25:51 #

I think this is a real great article post.Really thank you! Great.

回覆

EZ Battery Reconditioning Review
EZ Battery Reconditioning Review United States
2017/10/30 下午 12:21:03 #

Appreciate you sharing, great article post. Want more.

回覆

life leadership
life leadership United States
2017/11/1 下午 12:36:24 #

Fantastic article.Really looking forward to read more.

回覆

phenocal
phenocal United States
2017/11/1 下午 08:11:22 #

Very good article.Really thank you! Cool.

回覆

phentaslim review
phentaslim review United States
2017/11/3 下午 02:11:04 #

Really appreciate you sharing this article post.Really thank you! Keep writing.

回覆

sciatica pain ebook
sciatica pain ebook United States
2017/11/15 上午 09:24:48 #

Thanks again for the blog article.Much thanks again. Fantastic.

回覆

avocat criminel
avocat criminel United States
2017/11/16 下午 07:58:24 #

Thanks for the article. Really Cool.

回覆

swimwear
swimwear United States
2017/11/23 下午 11:30:16 #

Enjoyed every bit of your blog article. Really Cool.

回覆

Web Design Auckland
Web Design Auckland United States
2017/11/25 下午 08:25:05 #

I really like and appreciate your blog.Thanks Again. Really Cool.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 07:35:22 #

Thanks-a-mundo for the post.Thanks Again. Really Cool.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/27 上午 01:44:35 #

Appreciate you sharing, great blog post.Really looking forward to read more. Cool.

回覆

truck wreckers
truck wreckers United States
2017/11/29 下午 06:07:22 #

I value the blog. Will read on...

回覆

can ho 4 mat tien
can ho 4 mat tien United States
2017/11/30 上午 12:45:26 #

Really enjoyed this article post.Really thank you! Fantastic.

回覆

commercial real estate loan
commercial real estate loan United States
2017/12/1 上午 02:31:37 #

Awesome blog article. Cool.

回覆

porno
porno United States
2017/12/1 下午 06:14:20 #

I loved your blog article. Really Great.

回覆

business trade lines
business trade lines United States
2017/12/3 上午 06:27:46 #

Im grateful for the blog post.Much thanks again. Keep writing.

回覆

mobile porno
mobile porno United States
2017/12/5 上午 11:16:48 #

Thank you ever so for you article post.Really looking forward to read more.

回覆

Thanks for sharing, this is a fantastic article post. Much obliged.

回覆

http://instacheckpets.bid/story.php?id=11859
http://instacheckpets.bid/story.php?id=11859 United States
2017/12/10 上午 10:24:32 #

I am so grateful for your blog post.Really thank you! Will read on...

回覆

legal lawyer
legal lawyer United States
2017/12/10 下午 08:51:08 #

Really enjoyed this blog. Will read on...

回覆

Cameron Barsness
Cameron Barsness United States
2017/12/14 上午 10:57:32 #

Fantastic post.Much thanks again. Really Cool.

回覆

browse this site
browse this site United States
2017/12/14 下午 05:51:21 #

Im obliged for the blog.Really looking forward to read more. Want more.

回覆

canon driver software
canon driver software United States
2017/12/16 下午 07:31:22 #

A big thank you for your post.Really looking forward to read more. Great.

回覆

tips weight loss
tips weight loss United States
2017/12/17 上午 01:51:39 #

I appreciate you sharing this blog.Thanks Again. Awesome.

回覆

Technology
Technology United States
2017/12/17 下午 07:08:25 #

Very informative blog post.Really thank you! Really Great.

回覆

go to the website
go to the website United States
2017/12/18 上午 02:16:42 #

Very good blog article.Really thank you! Cool.

回覆

home based work
home based work United States
2017/12/20 下午 08:28:13 #

I truly appreciate this blog post.Thanks Again. Really Cool.

回覆

canon drivers
canon drivers United States
2017/12/23 上午 07:45:18 #

I am so grateful for your blog article.Thanks Again. Cool.

回覆

I loved your blog article.Really thank you! Want more.

回覆

hp driver
hp driver United States
2017/12/25 下午 07:44:41 #

Im grateful for the blog post.Really thank you! Keep writing.

回覆

Thanks so much for the blog article.Really thank you! Much obliged.

回覆

Thanks for the article post. Awesome.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 02:10:20 #

Wow, great blog.Thanks Again. Awesome.

回覆

canon drivers
canon drivers United States
2017/12/27 下午 08:23:14 #

Say, you got a nice blog.Much thanks again.

回覆

hp drivers
hp drivers United States
2018/1/2 上午 10:58:16 #

Thanks so much for the blog.Really looking forward to read more. Really Cool.

回覆

great post to read
great post to read United States
2018/1/2 下午 07:06:45 #

I appreciate you sharing this post.Thanks Again. Much obliged.

回覆

hp drivers
hp drivers United States
2018/1/3 上午 10:21:27 #

Im obliged for the blog article. Really Great.

回覆

colocation miami
colocation miami United States
2018/1/10 下午 12:07:44 #

I truly appreciate this post.Really looking forward to read more. Much obliged.

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List