Drop and create snapshot to access mirror database

Create_snapshot.sql

USE [master]
/*
Procedure Name			: USP_Create_Snapshot
Description				: Creates snapshot for the database whose name is passed a parameter to this SP
Execution Instruction	: EXEC dbo.USP_Create_Snapshot @DBName
Input Parameters		: @DBName- database name
*/

CREATE Proc [dbo].[USP_Create_Snapshot](@DBName sysname)
AS
Begin

declare @status varchar(20), @state varchar(20)
set @status=(select mirroring_state_desc from sys.database_mirroring where database_id=DB_ID(@DBName))

		
if @status<>'synchronized'
	begin
		print 'Snapshot cannot be created when the database is synchronizing. Please retry when the dataabse is synchronized'
	end

else
	begin

Declare @dbname2 varchar(10), @str nvarchar(4000),@space varchar(10)=NULL,@dbid int, @SnapDBName sysname
,@LName varchar(50), @FName varchar(150), @seq varchar(50)
set @dbname2='_snap'
Set @SnapDBName=@DBName+@dbname2

/*
Snapshot needs filenames with extension .ss. So replace ndf and mdf with ss and also append the timestamp to 
the snapshot name and its filenames so that we can keep multiple snapshots at a time with different names
*/
Set @seq='.ss'
Set @str='create database ['+@SnapDBName+'] ON '

/*Save logical and physical filename of all files except log file into variables so that we can use them in create statement.*/
declare cur cursor
for
select name, filename from master.sys.sysaltfiles where dbid=db_id(@dbName) and filename not like '%ldf%'

Open cur 
Fetch next from  cur into @LName, @FName

While @@FETCH_STATUS=0
Begin


if @FName like '%.mdf%' 
  set @FName= replace(@FName,'.mdf',@seq)

if @FName like '%ndf%' 
  set @FName= replace(@FName,'.ndf',@seq)

/*Prepare  create database statement with each filename specified*/
Set @str=@str+'(Name='+''''+@Lname+''''+', Filename='+''''+@FName+''''+')'

Fetch next from cur into @LName, @FName

if @@FETCH_STATUS=0
Set @str=@str+','
END
Close cur
Deallocate cur
Set @str=@str+' AS SNAPSHOT OF ['+@DBName+']'
EXEC(@str)
END
end
GO

Drop_snapshot.sql

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[USP_Drop_Snapshot]    Script Date: 12/29/2017 5:30:27 AM ******/

/*
Procedure Name			: USP_Create_Snapshot
Description				: Creates snapshot for the database whose name is passed a parameter to this SP
Execution Instruction	: EXEC dbo.USP_Drop_Snapshot @DBName
Input Parameters		: @DBName- database name
*/

CREATE PROC [dbo].[USP_Drop_Snapshot](@DBName sysname)
AS
BEGIN

declare @str varchar(200),@SnapName sysname

declare cur cursor
for
select name from sys.databases where source_database_id=DB_ID(@DBName)
Open cur
Fetch next from cur into @SnapName
while @@FETCH_STATUS=0

begin

/* Delete the snapshot */
Set @str='DROP DATABASE ['+@SnapName+']'
exec(@str)
Print @SnapName+' deleted'

fetch next from cur into @SnapName
END

Close cur
Deallocate cur

END

GO