SQL Server Database Snapshot

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:

SQL Server 2019

Database Snapshots