Wednesday, September 14, 2011

What is Database Snapshot

What is Database Snapshot?
1. Database snapshot is a static image of a database at particular point in time when snapshot was taken.
2. Snapshot is read-only and structures same as original database. It works based on sparse file technologies.
3. Snapshot database is good for reporting purposes.
4. Please remember there is also something called as "Snapshot Isolation Level" which is completely different from Database Snapshot, don't be confuse.

How To Create a Database Snapshot?
USE master
GO
CREATE DATABASE MSSQLFIX_SS
ON
(NAME=TEST,
FILENAME='C:\Program Files\DATA\MSSQLFIX_SS.ss'
)
AS SNAPSHOT OF MSSQLFIX
GO 

Architecture of Database Snapshot?
1. These files empty when the snapshot is taken which points to original database files.
2. Any modification to original database makes the data pages upload to stores files before modification.
3. The actual database cannot be dropped, you can only able to drop the database, once the snapshot deleted first.
4. The space occupied by the snapshot is nothing but the changed or modified data pages of the actual database. For unchanged pages, it still fetches the data from the actual database.

How to revert back the database from snapshot? 
USE master
GO
RESTORE DATABASE Databasename
FROM DATABASE_SNAPSHOT = 'Snapshot_Database';
GO

Snapshot Advantages.
1. Snapshot allows to provide historical data archiving used for reporting
2. Snapshots increase the performance by reducing impact on original database
3. Snapshot provides recovery of data in case of wrong data or last data by reverting the database

How to read data from Snapshot? 
USE database_name
GO
Select * from 'Table_Name'
GO 

Snapshot Limitations:
1. Backups, restores, detaching and dropping of original database are not supported as long as snapshot exists
2. Snapshot must exist within the same instance
3. To revert database from snapshot
     a. Multiple snapshots can’t be existed
     b. No compressed / read-only file groups in database.
Snapshot vs. LogShipping vs. Mirroring vs. Replication vs. Failover Clustering, database snapshots in SQL Server 2008, Introduction to Snapshot Database, Database Snapshots, Create a Database Snapshot , how to use databse snapshot, Snapshot Isolation Level in SQL Server , database snapshot in sql server 2008 r2
database snapshot in sql server 2005, how to create database snapshot in sql server 2012, database snapshot in sql server 2012, When and how to use Database Snapshots, Querying a Database Snapshot, Alternatives to snapshot functionality, Database Mirroring, Questions about accessing mirrored database, Revert a Database to a Database Snapshot, How Database Snapshots Work, View a Database Snapshot , View the Size of the Sparse File of a Database Snapshot, Database Mirroring and Database Snapshots, Simple Example of Snapshot

1 comments:

Post a Comment