Create a Database snapshot, Restore from snapshot, Delete the snapshot files

 use Test
go
declare
@Number INT,
@ssdirname varchar(1000),
@targetdb varchar(255),
@Drop Varchar(max)

Set @Number=1
SET @ssdirname = 'D:\DatabaseSnapshots\'
SET @targetdb = DB_NAME() +'Snapshot'+ Convert(Varchar(10),@Number)

---SET @targetdb = DB_NAME() +'Snapshot'+ Convert(Varchar(10),@Number)+Convert(Varchar(100),getdate(),112)

--print @targetDb


-- Unique timestamp for filenames
DECLARE @timestamp varchar(50)
SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(varchar(50),GETDATE(),126),':',''),'.',''),'-','')

DECLARE oncmd CURSOR FOR
select OnCmd = '(NAME=''' + [name] + ''', FILENAME=''' + @ssdirname + [name]  + @timestamp + '.ss'')'
from sys.database_files
where [type] = 0

DECLARE @oncmd varchar(500), @sqlcmd varchar(4000)
SET @sqlcmd = ''

OPEN oncmd
FETCH NEXT FROM oncmd INTO @oncmd
WHILE @@FETCH_STATUS = 0
BEGIN
IF @sqlcmd <> ''
SET @sqlcmd = @sqlcmd + ', ' + CHAR(10)
SET @sqlcmd = @sqlcmd + @oncmd

FETCH NEXT FROM oncmd INTO @oncmd
END
CLOSE oncmd
DEALLOCATE oncmd

SET @sqlcmd = 'CREATE DATABASE ' + @targetdb + ' ON ' + CHAR(10) + @sqlcmd
SET @sqlcmd = @sqlcmd + CHAR(10) + 'AS SNAPSHOT OF ' + DB_NAME()

------------------------------------------------------------------------------------
--STEP1:CREATE Database snapshot
IF EXISTS(select * from sys.databases where name='dbname')
DROP DATABASE dbname

--EXEC (@sqlcmd)
PRINT @SQlCMD
PRINT 'PUT YOUR CODE HERE '--TEST COMPLETE/Automation --- go to step 2


--ALTER DATABASE Test
-- SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--GO

--ALTER DATABASE test
-- SET MULTI_USER
--GO


--STEP2:
---Restore File

 Declare @RestoreCmd Varchar(max)
SET @RestoreCmd= 'RESTORE DATABASE Test FROM DATABASE_SNAPSHOT='''+  @targetdb    +  ''''
--EXEC (@sqlcmd)
print @RestoreCmd



USE MASTER
GO
/****** Object:  StoredProcedure [dbo].[SPRestoreSnapshot]    Script Date: 9/28/2015 5:21:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTEr PROCEDURE [dbo].[SPRestoreSnapshot]
AS

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    DECLARE @Number INT ,
        @ssdirname VARCHAR(1000) ,
        @targetdb VARCHAR(255) ,
        @Drop VARCHAR(MAX);


    SET @Number = 1;
    SET @ssdirname = 'D:\DatabaseSnapshots\';
    SET @targetdb = 'dbname' + 'Snapshot' + CONVERT(VARCHAR(10), @Number);


    DECLARE @RestoreCmd VARCHAR(MAX);
    SET @RestoreCmd = '
USE MASTER

    RESTORE DATABASE dbname FROM DATABASE_SNAPSHOT='''
        + @targetdb + '''';
    EXEC (@RestoreCmd)
ALTER DATABASE DB
SET MULTI_USER
;
--print (@RestoreCmd)








 PRINT 'WANTS TO delete, insert ,DROP '--GO TO STEP3


 --STEP3:
--DELETE THE Database SnapShot and the FILES associated with snapshot in the Folder
SET @DROP = 'DROP DATABASE ' + @targetdb + ';' + CHAR(10)
--EXEC (@DROP)
PRINT @DROP