Automatically Restore Database ( Backup databse)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[DBA_Recovery_FULL]
as
/*
Table Used :- master.dbo.sysdatabases
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Restore Full Backup of OldDatabase Database to another Database
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Begin
declare
@spid as nvarchar(4),
@killcommand as nvarchar(255) ,@unc as nvarchar(255)
while (select count(*) from master.dbo.sysprocesses pc
inner join master.dbo.sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame<>'sa' and pc.spid <> @@SPID and sd.name = 'NewDatabase')<>0
BEGIN
set @spid = (select top 1 pc.spid from master.dbo.sysprocesses pc
inner join master.dbo.sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame <> 'sa' and pc.spid <> @@SPID and sd.name = 'NewDatabase')
set @killcommand = 'kill ' + @spid
--print 'Killing ' + @spid
exec(@killcommand)
END

SELECT top 1 @unc = M.physical_device_name
FROM [msdb].[dbo].[backupset] S
JOIN [msdb].[dbo].[backupmediafamily] M ON M.media_set_id=S.media_set_id
WHERE S.database_name = 'OldDatabse'
and S.type ='D'
and M.physical_device_name like 'S:\TempBackup\Full_Backup%'
and convert(char(10),S.backup_start_date,120) = convert(char(10),getdate() ,120)
order by s.backup_finish_date desc

restore database NewDatabase
from disk = @unc
with replace,
move 'oldDatabase' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\NewDatabse.mdf',
move 'oldDatabase_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\NewDatabase_log.ldf'

ENd