Understanding how SQL Server database snapshots work page 1/2

How does a database snapshot work?



You can use the create database statement of a typical DATABASE command to generate a DATABASE snapshot. The Declaration contains an additional description of the source DATABASE snapshot. When a snapshot is created, a sparse file is generated at the same time. This file (only used in NTFS volumes) is not allocated to it during initialization-although you may see the file size in WINDOWS resource manager, it looks the same size as the original source database file. For disks, the file size is close to zero.



The data files read during database snapshot initialization are from the source database. When the data in the source database changes, the Data Engine copies the original data from the source database to the snapshot database. This technology ensures that the snapshot database only reflects the data status when the snapshot is executed. When the SELECT command is used to publish an apsaradb for RDS snapshot, no locks are released regardless of whether the data page is located in the source database data file or in the snapshot database data file. Because no locks are released in read-only database snapshots, database snapshots are an important solution for report solutions.


A snapshot instance



Now, let's take a look at how database snapshots work in SQL Server 2005. To do this, first I need a source database as the source of the snapshot. The following script creates a source database:


USE master
GO
If exists (SELECT name from
Sysdatabases where [name] = 'sourcedatabase ')
Drop database SourceDatabase
GO
Create database SourceDatabaseON PRIMARY
(
NAME = SourceDatabase_Data,
FILENAME = 'C: SQLServerSourceDatabase_Data.mdf'
) LOG ON
(
NAME = SourceDatabase_Log,
FILENAME = 'C: sqlserversourcedatabase_log.ldf'
)
GO






Note the size of the product area. I define its size as CHAR (150) to emphasize the growth level of data files, so that in my next instance it will be easier to explain how snapshots work.



Now that I have a source database, I load some data to expand the size of the data file. In this way, use the above script to create a sales history table.


USE SourceDatabase
GO
IF OBJECT_ID ('saleshistory ')> 0 DROP
TABLE SalesHistory
GO
Create table SalesHistory
(SaleID int identity (1, 1 ),
Product CHAR (150), SaleDate DATETIME,
SalePrice MONEY)
DECLARE @ I INT
SET @ I = 1
WHILE (@ I <= 10000)
Begin insert into SalesHistory
(Product, SaleDate, SalePrice)
VALUES ('computer ', DATEADD (mm, @ I, '2014/1/123 '),
DATEPART (MS, GETDATE () + (@ I + 57 ))
Insert into SalesHistory
(Product, SaleDate, SalePrice)
VALUES ('bigint', DATEADD (mm, @ I, '2017/123 '),
DATEPART (MS, GETDATE () + (@ I + 13 ))
Insert into SalesHistory
(Product, SaleDate, SalePrice)
VALUES ('pooltable', DATEADD (mm, @ I, '2017/123 '),
DATEPART (MS, GETDATE () + (@ I + 29 ))
SET @ I = @ I + 1
END
GO