SQL Server Database Snapshot provides a read-only, static view of a source database as it existed at snapshot creation
- Snapshots are dependent on the source database
- Snapshots must be on the same server instance as the database
- If the source database becomes corrupt or unavailable for any reason, all snapshots also become unavailable
- Database snapshots operate at the data-page level, which means that as each page of data is updated in the source database, it copies the original page from the source database to the snapshot
- This process is called a copy-on-write operation
- The snapshot stores the original page, preserving the data records as they existed when the snapshot was created
- Subsequent updates to records in a modified page do not affect the contents of the snapshot
- The copied original pages are stored in a sparse file
- Initially, a sparse file has not yet been allocated disk space for user data
- As more and more pages are updated in the source database, the size of the file grows
What is the purpose of the Database Snapshot?
- Snapshots can be used for reporting purposes – read-only db
- In the event of a user error on a source database (delete table), you can revert the source database to the state it was in when the snapshot was created
- If a deletion of a table occurs, then you can restore that point in time of data from the snapshot, rather than doing a full database backup followed by all transactional backups
--CREATE A NEW DATABASE
CREATE DATABASE DBS --<< PRIMARY SOURCE PRODUCTION DATABASE
GO
--CREATE TABLE NAMES
USE DBS
GO
CREATE TABLE NAMES (id INT, FNAME VARCHAR (20))
--INSERT DATA INTO TABLE
INSERT INTO NAMES VALUES (1,'ALBERT'),(2,'BOB'),(3,'CHARLES')
--VIEW DATA
SELECT * FROM NAMES
--VEIW ALL DATABASES, INCLUDING THE DBS
Select * from sys.databases
--VIEW THE LOGICAL NAME OF THE DATA FILE OF PRODUCTION DBS
Sp_helpdb 'DBS'
--CREATE A DATABASE SNAPSHOT OF DBS
CREATE DATABASE Snapshot_DBS ON --<< NAME OF THE SNAPSHOT DB
(Name ='DBS', --<< NAME OF THE LOGICAL DATA FILE
FileName='C:\Snapshots\Snapshot_DBS.ss') --<< FILE LOCATION OF THE SNAPSHOT WITH SS EXTENTION
AS SNAPSHOT OF DBS;
GO
--REVIEW THE SIZE OF DATABASE VIA GUI
--DROP TABLE ON PRIMARY PRODUCTION DATABASE DBS
USE DBS
GO
DROP TABLE NAMES
--RESTORE THE TABLE FROM THE SNAPSHOT
USE MASTER
GO
RESTORE DATABASE DBS
FROM DATABASE_SNAPSHOT = 'Snapshot_DBS'
--VERIFY RECOVERY OF TABLE
USE DBS
GO
SELECT * FROM NAMES
--INSERT NEW DATA INTO PRODUCTION DATABASE AND TABLE
USE DBS
GO
INSERT INTO NAMES VALUES (4,'EUGENE')
USE DBS
GO
SELECT * FROM NAMES
--DID IT UPDATE THE SNAPSHOT DATABASE AND TABLE??
USE Snapshot_DBS
GO
SELECT * FROM NAMES
--NO!!! AS THE SNAPSHOT IS ONLY CONTAINS A THE 'COPY' AT THE TIME OF THE SNAPSHOT CREATION
--CAN YOU EXECUTE UPDATE OR INSERT THE SNAPSHOT TABLE(S)?
USE Snapshot_DBS
GO
UPDATE NAMES
SET FNAME = 'It Dept'
--CANNOT UPDATE OR INSERT OR DELETE AS IT'S READ-ONLY
DROP DATABASE Snapshot_DBS
GO
Also See: