CREATE DATABASE SNAPSHOT GIVEN DATABASE

CREATE DATABASE SNAPSHOT GIVEN DATABASE

                            Sometimes you have noticed, when you need to create database snapshot of given database, then SQL Server management studio will not support that facility for your self.The only way is, you have to execute "create database snapshot" T-SQL script.
And at the same time, if your database contains many data files in different disks, then you have to take some time to create script for given database. 
And same as if you need to change snapshot location to different disk then, again you need to change it manually.

Hope this T-SQL script will help you above issues,

SET NOCOUNT ON

DECLARE @database_name nvarchar(128)='<Your Database Name>';

DECLARE @database_snapshot_location nvarchar(128)='';--if this is blank then snapshot create same location

DECLARE @ssql nvarchar(max)='';

SET @ssql='CREATE DATABASE ' +@database_name +'_SNAPSHOT  ON '

SELECT @ssql=@ssql+ssql_data FROM

(

SELECT  TOP (100) PERCENT N'( NAME = N'''+name+''' , FILENAME = '+CASE WHEN @database_snapshot_location='' THEN ' N'''+REPLACE(REPLACE(physical_name,'.mdf','_SNAPSHOT_DATA_FILE.ss'),'.ndf','_SNAPSHOT_DATA_FILE.ss') ELSE ''''+ @database_snapshot_location+name+'.ss' END+''' )'+CASE WHEN (MAX([file_id]) OVER(PARTITION BY database_id))<>[file_id] THEN ',' ELSE '' END+char(10)+char(13) ssql_data

FROM sys.master_files WHERE (database_id =DB_ID(@database_name)) AND ([type]=0) 

ORDER BY [file_id] 

) T;

SET @ssql=@ssql+' AS SNAPSHOT OF ' +@database_name +' ;'

PRINT @ssql

EXEC(@ssql);

    

Cheers...
and Most welcome your comments and ideas...