RiCo技術農場

 

假設今天我們要模擬正式環境測試資料更新效能、T-SQL或SP影響，可以先建立snapshots，等測試完畢後再還原。

建立snapshots

USE master;

GO

-- Create the database snapshot.

CREATE DATABASE [demotest_Snapshot] ON

(NAME = N'demotest01',

FILENAME = N'D:\sqldata\demotestSS01.mdf'),

(NAME = N'demotest02',

FILENAME = N'D:\sqldata\demotestSS02.ndf')

AS SNAPSHOT OF demotest;

GO

 

查看snapshot size

Dmv:sys.dm_io_virtual_file_stats

Catalog: sys.master_files

USE master;

GO

SELECT

db_name(vfstats.database_id) AS [Database Name],

vfstats.file_id AS [File ID],

mfiles.[name] AS [Database File Name],

mfiles.[name] AS [File Name],

vfstats.size_on_disk_bytes / 1024 AS [PHYSICAL Size (KB)],

mfiles.[size] * 8 / 1024 AS [LOGICAL Size (MB)],

mfiles.physical_name AS [Physical File Name]

FROM

sys.dm_io_virtual_file_stats(NULL, -1) AS vfstats

JOIN sys.master_files AS mfiles

ON vfstats.file_id = mfiles.file_id

WHERE

mfiles.database_id = vfstats.database_id

AND mfiles.database_id in (7,11)

ORDER BY [Database File Name];

GO

實體檔案很小

 

更新來源資料庫資料

update dbo.GIAD set ZIP='100' where ZIP between '200' and '300';

go

比對檔案內容(發現snapshot內容沒變)

SELECT

snapshot.ZIP,snapshot.CLIENT_ID,

snapshot.ZIP AS [Snapshot Value in DB_Snapshot],

source.ZIP AS [Source Value in SourceDB]

FROM

demotest_Snapshot.dbo.GIAD AS snapshot

JOIN demotest.dbo.GIAD AS source

ON snapshot.CLIENT_ID=source.CLIENT_ID

where source.ZIP ='100';

GO

再度查看檔案大小，得知SQLSERVER在更新資料前先snapshots來源資料，並寫入檔案。

嘗試還原

USE master;

GO

RESTORE DATABASE demotest

FROM DATABASE_SNAPSHOT = 'demotest_Snapshot';

GO

來源資料庫以正常還原

發現檔案大小並未縮小，反而增大

結論

利用snapshot 可以提高大量rollback作業效率，但snapshot存在的價值可不是用來備份及還原資料庫，

而且資料只要被複製到snapshot資料庫中，資料就會永久存在，檔案也會慢慢增長，直到被刪除。