SQL Server 2008 数据库快照

    数据库快照大家都知道吗？是不是有很多人没深入研究过呀。那么我们就来动手实验一下Sql Server 2008 的数据库快照功能吧！

    首先，先来简单介绍一下数据库快照。

    数据库快照其实也是一个数据库，不过他是系统定义好格式的，不需要手动创建，配置过程中会由系统自动创建，这点要先了解。

    其次，数据库快照使用NTFS分区所提供的稀疏文件技术，这样文件在逻辑上可以很大，但是物理上却非常小。数据库快照功能不会生成整个源数据库的完整副本，而是会将发生改变的数据页写入到快照数据库中。这样意味着如果数据库快照创建完成之后，源数据库没有发生任何更改，则快照数据库为空。这是数据库快照工作的基础。

    下面我们就来做一个实验，实验中我们使用SalesDB这样一个数据库。这里顺便跟大家介绍一下SQL Server2008企业版中给大家提供的数据表和索引的分区功能，所以我们第一步先对SalesDB创建4个文件组。

* SalesDB数据库，这个数据库数据量很大，完全备份192兆，这里就不给大家下载了，其实随便找一个数据库就行的。

创建文件组

USE SalesDB;
GO

-- Add first new filegroup with a single file.
ALTER DATABASE [SalesDB]
    ADD FILEGROUP SalesDBSalesDataPartition1;
GO

ALTER DATABASE [SalesDB]
    ADD FILE
        (NAME = N'SalesDBSalesDataPartition1',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition1.ndf',
        SIZE = 100,
        MAXSIZE = 120,
        FILEGROWTH = 10)
    TO FILEGROUP SalesDBSalesDataPartition1;
GO

PRINT 'Created SalesDBSalesDataPartition1';
GO

-- Add second new filegroup with a single file.
ALTER DATABASE [SalesDB]
    ADD FILEGROUP SalesDBSalesDataPartition2;
GO

ALTER DATABASE [SalesDB]
    ADD FILE
        (NAME = N'SalesDBSalesDataPartition2',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition2.ndf',
        SIZE = 100,
        MAXSIZE = 120,
        FILEGROWTH = 10)
    TO FILEGROUP SalesDBSalesDataPartition2;
GO

PRINT 'Created SalesDBSalesDataPartition2';
GO

-- Add third new filegroup with a single file.
ALTER DATABASE [SalesDB]
    ADD FILEGROUP SalesDBSalesDataPartition3;
GO

ALTER DATABASE [SalesDB]
    ADD FILE
        (NAME = N'SalesDBSalesDataPartition3',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition3.ndf',
        SIZE = 100,
        MAXSIZE = 120,
        FILEGROWTH = 10)
    TO FILEGROUP SalesDBSalesDataPartition3;
GO

PRINT 'Created SalesDBSalesDataPartition3';
GO

-- Add fourth new filegroup with a single file.
ALTER DATABASE [SalesDB]
    ADD FILEGROUP SalesDBSalesDataPartition4;
GO

ALTER DATABASE [SalesDB]
    ADD FILE
        (NAME = N'SalesDBSalesDataPartition4',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition4.ndf',
        SIZE = 100,
        MAXSIZE = 120,
        FILEGROWTH = 10)
    TO FILEGROUP SalesDBSalesDataPartition4;
GO

PRINT 'Created SalesDBSalesDataPartition4';
GO

-- Create a partition function for four partitions.
CREATE PARTITION FUNCTION Sales4Partitions_PFN(INT)
AS RANGE RIGHT FOR VALUES
    (2000000,
    4000000,
    6000000);
GO

-- Create a partition scheme using the partition function.
CREATE PARTITION SCHEME [Sales4Partitions_PS]
AS PARTITION [Sales4Partitions_PFN] TO
    (SalesDBSalesDataPartition1,
    SalesDBSalesDataPartition2,
    SalesDBSalesDataPartition3,
    SalesDBSalesDataPartition4);
GO

-- Rebuild the clustered index of Sales using the partitioning scheme.
CREATE UNIQUE CLUSTERED INDEX SalesPK ON Sales (SalesID)
    WITH (DROP_EXISTING = ON, ONLINE = ON)
    ON [Sales4Partitions_PS](SalesID);
GO

 

运行上面代码创建4个文件组。文件组可以将数据库根据索引定义规则，分别将对应数据存放到指定数据文件当中去，是相当实用的功能。如果你正在为数据文件把分区占满了发愁的话，那么仔细研究下上面的代码吧，你可以去其他分区创建新的数据文件，将新增加的数据分到其他分区当中去。

创建并检查数据库快照

1. 首先查看一下SalesDB数据库的数据文件列表

USE SalesDB;
GO

sp_helpfile;
GO

可以看到文件组的效果~

对应数据文件的大小

PRIMARY文件组中的文件为200MB,而且他文件为100MB

2. 创建快照数据库。

USE master;
GO

-- Create the database snapshot.
CREATE DATABASE [SalesDB_Snapshot] ON
    (NAME = N'SalesDBData',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\SalesDBData.mdf_SS'),
    (NAME = N'SalesDBSalesDataPartition1',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition1.ndf_SS'),
    (NAME = N'SalesDBSalesDataPartition2',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition2.ndf_SS'),
    (NAME = N'SalesDBSalesDataPartition3',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition3.ndf_SS'),
    (NAME = N'SalesDBSalesDataPartition4',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\Data\SalesDBSalesDataPartition4.ndf_SS')
AS SNAPSHOT OF SalesDB;
GO

创建数据库快照的时候使用Create database语句，另外需要加上AS SNAPSHOT OF <dbname> 选项。（充分说明数据库快照本身也是数据库）在为数据库快照命名的时候，建议名称中既包含源数据库名称，又能够包含快找数据库创建的时间。（因为快照是一个时间点的记录）

注意：在数据库快照的场景下，如果源数据库中有正在进行的事物，则这些事物必须在快照数据库中进行回滚以保证事物一致性

3. 查看快照数据文件大小

     查看数据库快照逻辑文件和物理文件大小的方式有很多，这里我们使用sys.dm_io_virtual_file_stats 动态视图 和 sys.master_files 目录视图。

USE master;
GO

SELECT
    db_name(ivfs.database_id) AS [Database Name],
    ivfs.file_id AS [File ID],
    mf.[name] AS [Database File Name],
    CASE
        WHEN mf.is_sparse = 0
            THEN 'No'
            ELSE 'Yes'
        END AS [Sparse File],
    mf.[name] AS [File Name],
    ivfs.size_on_disk_bytes / 1024 AS [PHYSICAL Size (KB)],
    mf.[size] * 8 / 1024 AS [LOGICAL Size (MB)],
    mf.physical_name AS [Physical File Name]
FROM
    sys.dm_io_virtual_file_stats(NULL, -1) AS ivfs
    JOIN sys.master_files AS mf
        ON ivfs.file_id = mf.file_id
WHERE
    mf.database_id = ivfs.database_id
    AND mf.database_id IN
        (db_id('SalesDB_Snapshot2'),
        db_id('SalesDB_Snapshot'),
        db_id('SalesDB'))
ORDER BY [Database File Name], [Sparse File];
GO

查询结果如下图

注意：虽然数据库快照文件的逻辑大小和源数据库的逻辑大小一致，但其物理大小仅为64k~192k之间。

 

处理多个快照

这个练习的目的：

• 查看源数据库更改对快照数据库的影响
• 查看对正在进行事物处理的数据库创建快照的效果
• 使用回复到快照状态的功能，快速回滚已提交的事物

查看源数据库更改对快照数据库的影响

1. 修改源数据库中的数据

USE SalesDB;
GO

-- Make a data change to a small set of data.
UPDATE Sales SET quantity = 42
    WHERE SalesID >= '4000000' AND SalesID < '4010000';
GO

2. 对比源数据库和快照数据库中的记录
SELECT
    snapshot.SalesID,
    snapshot.quantity AS [Snapshot Value in SalesDB_Snapshot],
    source.quantity AS [Source Value in SalesDB]
FROM
    [SalesDB_Snapshot].dbo.Sales AS snapshot
    JOIN SalesDB.dbo.Sales AS source
        ON snapshot.SalesID = source.SalesID
WHERE
    source.SalesID >= '4000000' AND source.SalesID < '4010000';
GO

（部分查询结果）

可以看到源数据库中被更新的行相应数值均变为42，而快照数据库中则仍然保留着之前的数值。在进行更新的过程中，包含之前数值的数据页被拷贝到了快照数据库的数据文件中去。

3. 执行前面查看文件大小的脚本，可以发现SalesDBDataSalesPartition3.ndf_ss文件的物理大小从64k增长到了448k

正像前面所看到的，数据库快照总是保持源数据库的静态拷贝，因此可以用于创建基于某个时间点的报表。如果源数据库更改不是很频繁，则采用数据库快照功能来保存数据库在多个时间的副本将是一个非常节省磁盘空间的方式。

查看对正在进行事物处理的数据库创建快照的效果

1. 使用事物执行更新

BEGIN TRAN
UPDATE Sales SET quantity = 56
    WHERE SalesID >= '4010000' AND SalesID < '4020000';
GO

注意：没有提交事物

2. 执行下面语句比较源数据库与快照数据库数据

SELECT
    snapshot.SalesID,
    snapshot.quantity AS [Snapshot Value in SalesDB_Snapshot],
    source.quantity AS [Source Value in SalesDB]
FROM
    [SalesDB_Snapshot].dbo.Sales AS snapshot
    JOIN SalesDB.dbo.Sales AS source
        ON snapshot.SalesID = source.SalesID
WHERE
    source.SalesID >= '4000000' AND source.SalesID < '4020000';
GO

可以看到源数据库中所有被更新的行相应的字段的数值均被更新为56，而快照数据库中对应的行则保留原数值。

3. 再次执行查看文件大小的脚本

可以看到 SalesDBSalesDataPartition3 .ndf_ss文件的物理大小从448k变成了704k，再次证明了数据页在源数据库被更改前复制到了快照数据库中去。

4. 回滚上次提交

ROLLBACK TRAN;
GO

5. 再次查看数据库比较结果

会发现，快照数据库中的数据恢复成了原有数值。

6. 再次查看数据文件大小

 

快照文件大小未发生变化。

结论：一旦数据被复制到了快照数据库中去，就会永久保留，直到快照数据库被删除。

使用回复到快照状态的功能，快速回滚已提交的事物

1. 执行命令，恢复数据库

USE master;
GO

RESTORE DATABASE SalesDB
    FROM DATABASE_SNAPSHOT = 'SalesDB_Snapshot';
GO

这时会发生错误。这是由于第二个快照数据库造成的。

2. 删除第二个快照数据库

DROP DATABASE SalesDB_Snapshot2;
GO

3. 再次恢复数据库

RESTORE DATABASE SalesDB
    FROM DATABASE_SNAPSHOT = 'SalesDB_Snapshot';
GO

这时操作成功，这时去源数据库查看的时候，会发现数据已经还原。

好了，到此为止吧。现在大家对数据库快照有一定认识了吧。睡觉去了。

 

作者：王文斌

2009年4月10日

转载请注明出处