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;
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
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
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.
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
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
- To learn more about database snapshots, read an article by Greg Robidoux, SQL Database Snapshots for Reporting, Auditing and Data Recovery.
- Jeffrey Yao wrote a piece on the new Extended Stored Procedures (ESP) in SQL Server 2019 called Discovering New System Objects and Functions in SQL Server 2019.
- Do you need to start taking copy-only backups of your demos? Atif Shehzad has an article called, Copy Only Backup for SQL Server.
