Creating Snapshots of a Database Script

Creating snapshots for databases is one of those features that is reserved for the Enterprise edition of SQL Server. A database snapshot is a read only transactionally-consistent copy of a database taken from a point-in-time. To create a snapshot of a database you need to execute a T-SQL statement; there is not an option through the UI. It is much the same as a CREATE DATABASE statement, but with a few differences:

  • you do not need to specify the log file
  • the files that you do create do not need to be of any particular file type (they are sparse files, a feature of NTFS file systems)
  • the database snapshot needs to reside on the same instance of SQL as the original database.

There are a few caveats to creating a snapshot, and so I’ve written a script that checks whether the database that is having a snapshot created meets these conditions. If it does it dynamically creates the create script which can be run. You can override the checks if you want to, but you probably won’t be able to run the command. Some of hte info below is verbatim from MSDN for reference purposes.

DECLARE @currentsnapshot SYSNAME
DECLARE @u_id sysname
DECLARE @edition sql_variant
DECLARE @skipchecks BIT

--You can skip all the checks by setting this to 0.
SET @skipchecks = 1

SET @dbname = 'CYA'

IF @skipchecks = 1

IF (cast (@edition as varchar (64)) not like 'Enterprise%') or (cast (@edition as varchar (64)) not like 'Developer%')
 PRINT '--Error! This edition of Sql does not support snapshots'+CHAR(10)

IF @dbname NOT IN (SELECT name FROM master.sys.databases) OR LEN(@dbname) = 0
 PRINT '--Error! The database [' + @dbname + '] does not appear to exist on the server.' +CHAR(10)

IF @dbname IN ('master','model','tempdb')
 PRINT '--Error! You cannot create snapshots on Master, Model and TempDb.' +CHAR(10)

if (select state_desc from master.sys.databases where name = @dbname) != 'ONLINE'
and (select mirroring_state from sys.database_mirroring
where database_id = db_id(@dbname)) IS NULL
 PRINT '--Error! '+ @dbname + ' is not ONLINE.'+CHAR(10)

if exists (select mf.state_desc, f.is_read_only from sys.master_files mf
inner join sys.filegroups f on f.data_space_id = mf.data_space_id
where database_id = db_id(@dbname)
AND mf.state_desc != 'ONLINE'
OR f.is_read_only != 0)
PRINT '/* Warning! Database '+@dbname+' contains offline or read-only filegroups.
 Offline Filegroups:
 * You cannot bring a filegroup online in a database that has any database snapshots.
 * If a filegroup is offline at the time of snapshot creation or is taken offline while a database snapshot exists, the filegroup remains offline.
 * This is because bringing a file back online involves restoring it, which is not possible if a database snapshot exists on the database.
 * Sparse files are not created for the offline filegroups.
 Read-Only Filegroups:
 * Reverting is unsupported for read-only filegroups and for compressed filegroups.
 * Attempts to revert a database containing either of these types of filegroups fail.

if exists (select a.role from sys.dm_hadr_availability_replica_states a
inner join sys.dm_hadr_database_replica_states d on a.group_id = d.group_id
where database_id = db_id(@dbname)
and role <> 0
print '/* Error! Database '+@dbname+' is in an Availability Group, and currently is not in a state that allows snapshot to be created.
 Replica Role:
 * You can create a database snapshot on an primary or secondary database in an availability group.
 * The replica role must be either PRIMARY or SECONDARY, not in the RESOLVING state.
*/ '+CHAR(10)

if exists (select synchronization_health from sys.dm_hadr_database_replica_states
where database_id = db_id(@dbname)
and synchronization_health <> 0)
print '/* Warning! Database '+@dbname+' is in an Availability Group, and currently is not in an optimised state for creating snapshots

 Synchronization State:
 * We recommend that the database synchronization state be SYNCHRONIZING or SYNCHRONIZED when you create a database snapshot.
 * However, database snapshots can be created when the database synchronization state is NOT SYNCHRONIZING.
*/ '+CHAR(10)

if exists(select name from sys.databases where source_database_id = db_id(@dbname))
DECLARE snap_cursor CURSOR
with cte (dbSnapshot)
select name from sys.databases where source_database_id = db_id(@dbname)
SELECT dbSnapshot from cte
OPEN snap_cursor
FETCH NEXT FROM snap_cursor into @currentsnapshot;
PRINT '-- Warning! ' + @currentsnapshot+' snapshot already exists for database '+@dbname +CHAR(10)
FETCH NEXT FROM snap_cursor into @currentsnapshot
CLOSE snap_cursor
DEALLOCATE snap_cursor;

 PRINT '/* Multiple snapshots can exist on a given source database. However
 Each database snapshot persists until it is explicitly dropped by the database owner.'+CHAR (10)+
 'Reverting is unsupported under the following conditions:
 * The source database contains any read-only or compressed filegroups.
 * Any files are offline that were online when the snapshot was created.
 * More than one snapshot of the database currently exists.
 * Only the snapshot that you are reverting can exist. */ '+CHAR(10)

if exists (select database_id from sys.database_mirroring dm
where database_id = db_id(@dbname)
and dm.mirroring_state_desc <> 'SYNCHRONIZED'
and dm.mirroring_role_desc = 'MIRROR')
PRINT '--Error! Database '+@dbname+' is a mirror however is not in a synchronized state. Wait until synchronized then try again'

if exists(select name FROM sys.master_files WHERE type= 2
 AND database_id = db_id(@dbname))
DECLARE filestream_cursor CURSOR
with cte (dbFileStream)
select name FROM sys.master_files WHERE type = 2
 AND database_id = db_id(@dbname)
SELECT dbFileStream from cte
OPEN filestream_cursor
FETCH NEXT FROM filestream_cursor into @filestream;
PRINT '--Warning! A Filestream called ' + @filestream+' exists for database '+@dbname+'. This will not be included in the snapshot.' +CHAR(10)
FETCH NEXT FROM filestream_cursor into @filestream
CLOSE filestream_cursor
DEALLOCATE filestream_cursor;
PRINT '/* SQL Server does not support database snapshots for FILESTREAM filegroups.
 If a FILESTREAM filegroup is included in a CREATE DATABASE ON clause,
 the statement will fail and an error will be raised.
 When you are using FILESTREAM, you can create database snapshots of standard
 (non-FILESTREAM) filegroups. The FILESTREAM filegroups are marked as
 offline for those database snapshots.*/ '+CHAR(10)

ELSE PRINT 'I know what I am doing'+CHAR(10)

-- The business end of the script
-- It will create the snapshot using the datetime in seconds
-- It will create sparse files in the same location as the original files
-- sparse files are a feature of NTFS file systems, and as such, snapshots can only be created on NTFS disks
-- Initially sparse files contain no data. Over time the space will increase.
-- To learn the number of bytes each sparse file of the snapshot is currently using on disk, you can use the size_on_disk_bytes column of the sys.dm_io_virtual_file_stats dynamic management view.
-- Alternatively, to see the disk space used by a sparse file, you can right-click the file in Microsoft Windows, click Properties, and look at the Size on disk value.
-- Except for file space, a database snapshot consumes roughly as many resources as a database.

SET @snapshot = @dbname + '_' + @u_id
SET @sql = 'CREATE DATABASE [' + @snapshot + '] ON '
SELECT @sql = @sql +CHAR(10)+'(NAME = ''' + RTRIM(LTRIM(name))
 + ''', FILENAME = ''' + RTRIM(LTRIM(physical_name)) +'_'+ @u_id + '_sf''),'
 FROM sys.master_files WHERE type <> 1 -- no need for log
 AND type <> 2 --cannot backup filestream filegroups
 AND database_id = db_id(@dbname)
SET @sql = SUBSTRING (@sql, 1, (LEN (@sql)-1))
SET @sql = @sql + CHAR(10) + ' AS SNAPSHOT OF [' + @dbname +']'
PRINT @sql

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s