How does a database snapshot work?
A database snapshot can be generated using a typical database command create DB statement, with an additional description of the source database snapshot in the declaration. When the snapshot is established, a sparse file is generated at the same time. This file (which can only be used on NTFS volumes) does not have disk space assigned to it during initialization-although you may see the size of the file in Windows Explorer, it looks the same size as the original source database file. In fact, the size of this file is close to zero for disk.
The data files that the database snapshot reads at initialization are from the source database. When the data for the source database changes, the data engine copies the original data from the source database to the snapshot database. This technique ensures that the snapshot database reflects only the state of the data when the snapshot is executed. When the Select command is used to publish against a database snapshot, the data page reads whether it is positioned in the source database data file or in the snapshot database data file without a lock being published. Because no locks are published in a read-only database snapshot, database snapshots are an important solution for report solutions.
An instance of a snapshot
Now, let's take a look at how database snapshots work in SQL Server 2005. To do this, I first 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 here. I define its size char (150) to emphasize the growth of the data file, so it's easier to explain how the snapshot works in my next example.
Now that I've got a source database, I'm loading some data to extend the size of the data file. So, use the script above to create the 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, 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, ' 3/11/1919 '),
DATEPART (MS, GETDATE ()) + (@i + 57))
INSERT into Saleshistory
(Product, Saledate, Saleprice)
VALUES (' Bigscreen ', DATEADD (mm, @i, ' 3/11/1927 '),
DATEPART (MS, GETDATE ()) + (@i + 13))
INSERT into Saleshistory
(Product, Saledate, Saleprice)
VALUES (' pooltable ', DATEADD (mm, @i, ' 3/11/1908 '),
DATEPART (MS, GETDATE ()) + (@i + 29))
SET @i = @i + 1
End
Go
Current 1/2 page
12 Next read the full text
