Uses of Database Snapshot
Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting with improved performance. In some cases, running queries on the mirror database might free up resources on the principal.
Access to historical data for report generation, a database snapshot provides a static view of a database. A snapshot can be useful to extend access to “historical” data from a particular point in time.
Creating a snapshot on the database would help protecting the administrative errors, like doing major updates, bulk updates, or dropping a table.
Spars Files
SnapShots are stored in spars files. Initially, these spars files are empty shells that do not contain any data, but as data updates happen on the database, these spars files fill-up with snapshot pages (Original data pages). If a database snapshot runs out of space, it is marked as SUSPECT. In this situation, the snapshot becomes unusable and must be dropped.
Create a SnapShot
CREATE DATABASE PUBS_SS ON
( NAME = PUBS_Data,
FILENAME = ‘D:MSSQLMSSQLDATAPUBS_DATA_1000.SS’)
AS SNAPSHOT OF PUBS;
GO
How SnapShot Works
When there is an update on the database, before it writes to the original page, the original page is copied to the SnapShot and then the database page is updated. The size of snapshot spars file depends on what amount of pages are updated in the database during the life of the SnapShot.
If database updates are confined to a small subset of pages that are updated very often, then snapshot growth slows down over a period of time. As once the original page is copied to snapshot, any updates to that specific page will not affect the snapshot anymore. But in the reverse case, if most of the original pages are being updated then snapshots size grows at fast rate.
Restoring from a SnapShot
RESTORE DATABASE PUBS from
DATABASE_SNAPSHOT = ‘ PUBS_SS’
GO
Copyright 2004 by the author. Published with the express permission of the autho
]]>