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 @dbname SYSNAME DECLARE @snapshot SYSNAME DECLARE @currentsnapshot SYSNAME DECLARE @sql VARCHAR(MAX) DECLARE @u_id sysname DECLARE @db_id INT DECLARE @edition sql_variant DECLARE @filestream SYSNAME DECLARE @skipchecks BIT --You can skip all the checks by setting this to 0. SET @skipchecks = 1 SET @dbname = 'CYA' IF @skipchecks = 1 BEGIN IF (cast (@edition as varchar (64)) not like 'Enterprise%') or (cast (@edition as varchar (64)) not like 'Developer%') BEGIN PRINT '--Error! This edition of Sql does not support snapshots'+CHAR(10) RETURN END IF @dbname NOT IN (SELECT name FROM master.sys.databases) OR LEN(@dbname) = 0 BEGIN PRINT '--Error! The database [' + @dbname + '] does not appear to exist on the server.' +CHAR(10) RETURN END IF @dbname IN ('master','model','tempdb') BEGIN PRINT '--Error! You cannot create snapshots on Master, Model and TempDb.' +CHAR(10) RETURN END 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 BEGIN PRINT '--Error! '+ @dbname + ' is not ONLINE.'+CHAR(10) RETURN END 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) BEGIN PRINT '/* Warning! Database '[email protected]+' 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. */'+CHAR(10) END 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 ) begin print '/* Error! Database '[email protected]+' 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) RETURN end if exists (select synchronization_health from sys.dm_hadr_database_replica_states where database_id = db_id(@dbname) and synchronization_health <> 0) begin print '/* Warning! Database '[email protected]+' 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) end if exists(select name from sys.databases where source_database_id = db_id(@dbname)) begin DECLARE snap_cursor CURSOR FOR with cte (dbSnapshot) AS ( 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; WHILE @@FETCH_STATUS = 0 BEGIN PRINT '-- Warning! ' + @currentsnapshot+' snapshot already exists for database '[email protected] +CHAR(10) FETCH NEXT FROM snap_cursor into @currentsnapshot END 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) END 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') BEGIN PRINT '--Error! Database '[email protected]+' is a mirror however is not in a synchronized state. Wait until synchronized then try again' RETURN END if exists(select name FROM sys.master_files WHERE type= 2 AND database_id = db_id(@dbname)) begin DECLARE filestream_cursor CURSOR FOR with cte (dbFileStream) AS ( 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; WHILE @@FETCH_STATUS = 0 BEGIN PRINT '--Warning! A Filestream called ' + @filestream+' exists for database '[email protected]+'. This will not be included in the snapshot.' +CHAR(10) FETCH NEXT FROM filestream_cursor into @filestream END 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) END END 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 @u_id = CONVERT(VARCHAR(10),GETDATE(),112) +'_'+ REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','') 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