How to dynamically create a SQL Server snapshot database

create-sql-server-db-snapshot.sql

declare @sql varchar(max), @on_clause varchar(max)

SELECT
    @on_clause = CASE
        WHEN @on_clause IS NULL THEN 'NAME = ' + name + ', FILENAME = ''' + physical_name + '.ss'''
        ELSE @on_clause + ', ' + 'NAME = ' + name + ', FILENAME = ''' + physical_name + '.ss'''
    END
FROM
    sys.database_files df
	where type_desc = 'ROWS'

set @sql = 'CREATE DATABASE ' + DB_NAME() + '_snapshot ON (' + @on_clause + ') AS SNAPSHOT OF ' + DB_NAME()

select @sql

exec(@sql)