Useful scenarios
Snapshots can be used in conjunction with database mirroring for reporting purposes. You can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot.
Maintaining historical data for report generation. You can create a database snapshot at the end of a given time period (such as a financial period) for later reporting.
Safeguarding data against administrative and user error. A database can be reverted back to the state it was in when the snapshot was created. Reverting is potentially much faster for this purpose than restoring from a backup.
In a test environment, it can be useful to quickly return a database back to the state it was prior a test run.
Note: Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server
How Database Snapshots Work?
When the snapshot is first created, the sparse file is empty as shown below. When data is read from the snapshot, it is actually being read from the primary database files. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file, so this way the data stays static.
The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.
When the first update operation happens, the original data is copied into the sparse file. The database engine then updates the data in the source database.
Example
--************ Database Snapshot ************
--1. Create
CREATE DATABASE DB_Snapshot_2009 ON (
NAME = 'test',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AWDB_Snapshot1.ss')
AS SNAPSHOT OF test; -- database name
--2.Recovery
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = 'DB_Snapshot_2008'
--3.Drop
Drop database AWDB_Snapshot_20080522
No comments:
Post a Comment