Database snapshot is a read-only static database which provides the view of data at a point in time. Snapshots use the process of copy on write operation. If a page in the source database is modified for the first time, the original page is copied from the database to the snapshot. Updates after that don’t affect the contents of the snapshot.
Snapshots are very useful to off load historical reports from the OLTP systems. If mirroring is used for high availability, snapshots can be created on the mirror database.
SQL server management studio does not provide an option to create snapshots. The following T-SQL creates a snapshot named ProductSnap_0100 (0100 is the time stamp of when the database is created i.e. 1am) on the Products database with its sparse file named Products_data.ss.
--Drop if the snapshot exists
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ProductSnap_0100')
DROP DATABASE ProductSnap_0100
--Create a snapshot on CORIS database
CREATE DATABASE ProductSnap_0100
ON (NAME = N'Products_Data', FILENAME = N'E:\Snapshot\Products_data_0100.ss')
AS SNAPSHOT OF Products
Here is an excellent blog about how to switch the reports to the new snapshot.
No comments:
Post a Comment