Automatic Restore

DECLARE @database_name VARCHAR(40),@backup_name VARCHAR(200),@Divice_name VARCHAR(200),@size BIGINT,
@destenation_siz int

SELECT TOP (1) newid() AS dynamic_id,
CONVERT(VARCHAR(40), K.backup_finish_date, 103) AS B_date,
 K.database_name,
 K.name,
 K.type,
 M.physical_device_name,
 CONVERT(bigINT, K.[backup_size]) / 1024 / 1024 AS BackUP_Size_MB
 INTO #temp 
FROM msdb..backupset K
 jOIN msdb..backupmediafamily M ON M.media_set_id = K.media_set_id
 AND K.backup_finish_date = ( SELECT MAX(backup_finish_date)
 from msdb..backupset
 WHERE database_name = K.database_name
 AND type = K.type
 )
WHERE K.name <> 'tempdb' 
AND TYPE='D'
AND CONVERT(bigINT, K.[backup_size]) / 1024 / 1024 BETWEEN 1 AND 1000
ORDER BY 1
SELECT @database_name=database_name,@backup_name=[name],@Divice_name=physical_device_name,@size=BackUP_Size_MB FROM #temp
CREATE TABLE #temp_2 (drive VARCHAR(10),GB int)

INSERT INTO #temp_2 EXEC xp_fixeddrives
SELECT @destenation_siz=GB FROM #temp_2
WHERE DRIVE='D'
IF (@destenation_siz>@size)
BEGIN
    
    DECLARE @s1 VARCHAR(2000)
    
        SET @s1='DECLARE @dfile VARCHAR(60)
                 DECLARE @s1 VARCHAR(300)
            SELECT @dfile=[name] FROM '+@database_name+'.sys.database_files
            WHERE TYPE=0 
            DECLARE @lfile VARCHAR(60)
                 DECLARE @s2 VARCHAR(300)
                 SELECT @lfile=[name] FROM '+@database_name+'.sys.database_files
        WHERE TYPE=1
            create table ##temp_3 (ldf_file varchar(60),mdf_file varchar(60))
            insert into ##temp_3 values (@lfile,@dfile)
            '
    exec (@s1)
    DECLARE @dfile VARCHAR(60)
    DECLARE @lfile VARCHAR(60)
    SELECT @dfile=mdf_file,@lfile=ldf_file FROM ##temp_3
    
    DECLARE @string VARCHAR(600)
    SET @string='
    RESTORE DATABASE '+@database_name+' FROM 
    DISK = N'''+@Divice_name+''' WITH REPLACE, 
    MOVE N'''+@dfile+''' TO N''D:\ProcedureTest\'+@dfile+'test.mdf'', 
    MOVE N'''+@lfile+''' TO N''D:\ProcedureTest\'+@lfile+'test.ldf'', NOUNLOAD, STATS = 10'
    print (@string)
    
END


DROP table #temp
DROP TABLE #temp_2
DROP table ##temp_3