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 '+@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.
 */'+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 '+@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)
 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 '+@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)
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 '+@dbname +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 '+@dbname+' 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 '+@dbname+'. 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