SQL Server Database Snapshots for Rapid Restores

By:   |   Updated: 2023-09-13   |   Comments   |   Related: More > Backup


Problem

Call me weird, but I enjoy building datasets for demos and testing code. Often the databases are enormous. When testing different columnstore compression options, one database swallowed 2TB. Taking a database backup is a good idea in case something goes wrong. Creating a copy-only backup is a poor choice when my test data consists of documents or images. No one wants to wait 15 minutes for a restore, especially during a live demo.

Solution

I'll explore database snapshots as an alternative to traditional backup and restore. We'll look at what snapshots are and when to use them. My ideal for reverting is the RESTORE DATABASE syntax. There's one use case where snapshots are my first choice. I'll touch on the downside and when to stick with the traditional mode. In writing this, I aim to ensure you know how helpful snapshots are, given the right circumstances.

Backup Your Demos

When I create a demo, there are many steps to highlight. For example, to compare row, page, and columnstore compression, I restore to a baseline after each attempt. Without a backup, you need to rebuild the dataset each time. No one has time for that unless your boss pays by the hour.

If your demo dataset consists of documents or images, a copy-only backup consumes space and time. The reason is SQL Server fails to compress these objects. This is where a snapshot shines.

What's a Database Snapshot

You take and test database backups as a full-time or part-time DBA. Snapshots are another type of backup that allows you to revert the database back to the time the snapshot was taken. They're a read-only, static copy of the database at the time the snapshot was taken. According to my research, snapshots are available in any modern edition of SQL Server.

This article focuses on the perspective of using them for demo databases. Countless online pieces cover backup and restore best practices. Microsoft is adamant snapshots don't substitute your standard backup strategy.

There's no way to create a snapshot via the user interface in Management Studio. The good news is the syntax for creating a snapshot is simple. Plus, like friends, doing anything with T-SQL is better. I've included a sample below.

--source: https://www.mssqltips.com

CREATE DATABASE SnapshotFun_snap
ON
    (
        NAME = SnapshotFun,
        FILENAME = N'C:\code\MSSQLTips\SQLFiles\SnapshotFun.ss'
    ) AS SNAPSHOT OF SnapshotFun;
Snapshot SSMS

Where could it be? After you create the snapshot, expand the snapshot folder to view it. The physical file resides at whatever location you define.

Build Your Demo Dataset

The code below builds a large dataset. Don't run it unless you have a spare 150GB sitting around. I've warned you, so don't comment on how I destroyed your drive. You'll end up with one table full of documents. You can download the .pdf from this link. Feel free to substitute any 20MB file. No one sets out to fill a database with documents, but life happens.

--source: https://www.mssqltips.com
USE [master];
GO

DROP TABLE IF EXISTS #DocumentHolder;

CREATE TABLE #DocumentHolder (Documents VARBINARY(MAX) NOT NULL);

INSERT INTO #DocumentHolder (Documents)
VALUES
(
    (
        SELECT *
        FROM
            OPENROWSET(BULK 'C:\code\MSSQLTips\SQLFiles\ImportantDocument.pdf', SINGLE_BLOB)
            AS ImportantDocument
    ));


IF DATABASEPROPERTYEX('SnapshotFun_snap', 'Version') IS NOT NULL
BEGIN
    DROP DATABASE SnapshotFun_snap;
END;
GO

IF DATABASEPROPERTYEX('SnapshotFun', 'Version') IS NOT NULL
BEGIN
    ALTER DATABASE SnapshotFun SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SnapshotFun;
END;
GO

CREATE DATABASE SnapshotFun
ON PRIMARY
       (
           NAME = N'SnapshotFun',
           FILENAME = N'C:\code\MSSQLTips\SQLFiles\SnapshotFun.mdf',
           SIZE = 66000000KB,
           FILEGROWTH = 1000000KB
       )
LOG ON
    (
        NAME = N'SnapshotFun_log',
        FILENAME = N'C:\code\MSSQLTips\SQLFiles\SnapshotFun_log.ldf',
        SIZE = 4000000KB,
        FILEGROWTH = 500000KB
    );
GO

ALTER DATABASE SnapshotFun SET RECOVERY SIMPLE;
GO

USE SnapshotFun;
GO

CREATE TABLE dbo.SnapshotFun (ID INT IDENTITY(1, 1) NOT NULL, DocumentValue VARBINARY(MAX) NOT NULL);
GO

DECLARE @Count INT = 0;

WHILE @Count < 15
BEGIN
    INSERT INTO dbo.SnapshotFun (DocumentValue)
    SELECT TOP (200)
           Documents AS DocumentValue
    FROM #DocumentHolder dh
        CROSS JOIN sys.all_columns AS c1;

    SET @Count = @Count + 1;

    CHECKPOINT;

END;
GO  

Since creating this dataset takes four minutes, I'll make a backup to restore after making modifications. Below is the code to perform a copy-only backup with compression.

--source: https://www.mssqltips.com

BACKUP DATABASE SnapshotFun
TO  DISK = N'C:\code\MSSQLTips\SQLFiles\Snapshot.bak'
WITH COPY_ONLY,
     COMPRESSION,
     NOFORMAT,
     INIT,
     NAME = N'SnapshotFun-Full Database Backup',
     STATS = 10;
GO
Backup Time
File size

From the screenshot, you can see it took about two minutes to create our backup. The backup is nearly the same size as the .mdf file. Remember, SQL Server can't compress documents. Imagine we did something fabulous but want to restore it. We'll run the syntax below to do that.

--source: https://www.mssqltips.com

USE [master];

ALTER DATABASE SnapshotFun SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE SnapshotFun
FROM DISK = N'C:\code\MSSQLTips\SQLFiles\Snapshot.bak'
WITH FILE = 1,
     NOUNLOAD,
     REPLACE,
     STATS = 10;
ALTER DATABASE SnapshotFun SET MULTI_USER;
GO

Not bad; our restore took four minutes. Full transparency, my hard drive is a beast. Let's try and do better.

Create a Snapshot

Before creating the snapshot, I'll delete the huge .bak file. In SQL Server 2019, Microsoft introduced stored procedures for creating, copying, moving, and deleting files. That's right, no more xp_cmdshell for me.

--source: https://www.mssqltips.com

EXEC master.sys.xp_delete_files 'C:\code\MSSQLTips\SQLFiles\Snapshot.bak';
GO

Faster than quarters vanish at an arcade; it's gone.

The code below creates the snapshot. With file explorer open, you'll see it appear right before your eyes.

--source: https://www.mssqltips.com

CREATE DATABASE SnapshotFun_snap
ON
    (
        NAME = SnapshotFun,
        FILENAME = N'C:\code\MSSQLTips\SQLFiles\SnapshotFun.ss'
    ) AS SNAPSHOT OF SnapshotFun;
GO
Snapshot file size

It completed so fast you'll think something went wrong. Rest assured; our snapshot file sits in the designated folder. Don't let the file size scare you. Since, the snapshot is a sparse file, the size on disk is only 128KB when it is first created.

File Size

A snapshot grows in relation to the number of page updates in the source database. For fun, I'll drop our big table.

--source: https://www.mssqltips.com
DROP TABLE SnapshotFun.dbo.SnapshotFun;
GO

Guess I'll have to wait a few minutes to restore, right? Guess again.

--source: https://www.mssqltips.com

USE [master];

ALTER DATABASE SnapshotFun SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE SnapshotFun FROM DATABASE_SNAPSHOT = 'SnapshotFun_snap';
GO
Restore a Snapshot

Your eyes don't lie; it took one second to get our data back. Since we're done playing, execute the code below to clean up; your hard drive thanks you.

--source: https://www.mssqltips.com

USE [master];

IF DATABASEPROPERTYEX('SnapshotFun_snap', 'Version') IS NOT NULL
BEGIN
    DROP DATABASE SnapshotFun_snap;
END;
GO

IF DATABASEPROPERTYEX('SnapshotFun', 'Version') IS NOT NULL
BEGIN
    ALTER DATABASE SnapshotFun SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SnapshotFun;
END;
GO

Snapshot Downside

Bob Dylan said, "In every advantage, there's a disadvantage." He wasn't talking about SQL Server, but snapshots are no different. Creating multiple snapshots and changing the source database can consume a lot of space. Imagine a 2TB database with two snapshots. If you add or change 1TB of the source database, each snapshot file will grow by that amount. This is so the snapshot can maintain what the database looked like at the time the snapshot was taken. There goes your free space faster than a new version of SQL Server needs a patch. When creating demo datasets, I only create multiple snapshots if I know the changes to the source are minimal. Also, make sure the snapshots don't hang around for a long time, because as changes to the source database occur the snapshot needs to keep track of these too which will consume more disk space.

I'll use a standard copy-only backup whenever SQL compresses my file to a fraction of the size without a snapshot.

In the ideal situation, snapshots are terrific. Let me know in the comments below how you use snapshots or if I'm missing an even better solution for creating demo datasets.

Key Takeaways

  • If you're putting together a demo and there's a chance you need to start over, create a backup. You'll thank me, especially if there's an audience.
  • When the demo data compresses, choose standard copy-only backups.
  • If you're working with documents or images, consider snapshots. If you create multiple snapshot, they grow in relation to the changes made to the source database.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jared Westover Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-09-13

Comments For This Article