Skip to content

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