Restore last backup to a different server/database

/*
Script to restore the last backup of the source database specified below onto the destination database on the current server

WARNING : Will attempt to kill all non-system users in the destination database!

Author : Kris Lendon
Date Created : 28/03/2003
Version: 1.0

*/
declare @sourceserver as nvarchar(255),
@sourcedb as nvarchar(255),
@destinationdb as nvarchar(255),
@systemaccount as nvarchar(255)
/*---------------------------------------------------- Change Parameters Block Begin -----------------------------------------------*/
set @sourceserver='sourceserver'--Server where the backup was executed
set @sourcedb='production'--Source Database to copy from
set @destinationdb='testing'--Destination Database (Will be overwritten)
set @systemaccount='DOMAIN\SQLSystem'--Service Account details (Needed so the script will not attempt to kill system processes)

/*---------------------------------------------------- Change Parameters Block End -------------------------------------------------*/
--Check if source server exists and is a registered linked server
if ((select count (*) from sysservers where srvname = @sourceserver)=0)
begin
raiserror('Source server does not exist or is not a registered linked server',1,1)
goto finish
end

--Check if the Destination Database exists
if ((select count (*) from sysdatabases where name = @destinationdb)=0)
begin
raiserror('Destination Database does not exist',1,1)
goto finish
end

--Find the last backup in the set for the source database

declare @physicalname as nvarchar(255),
@loglogical as nvarchar(255),
@datalogical as nvarchar(255),
@spid as nvarchar(4),
@killcommand as nvarchar(255),
@cmd as nvarchar(255),
@unc as nvarchar(255),
@exec as nvarchar(1000)


create table #backupfile
(
filename nvarchar(255)
)

set @exec ='select top 1 physical_device_name from [' + @sourceserver + '].msdb.dbo.backupfile bf
inner join [' + @sourceserver + '].msdb.dbo.backupset bs on bf.backup_set_id=bs.backup_set_id
inner join [' + @sourceserver + '].msdb.dbo.backupmediafamily bm on bs.media_set_id=bm.media_set_id
where bf.file_type=''D'' and bs.database_name=''' + @sourcedb + ''' and bs.server_name=''' + @sourceserver + ''' 
order by bs.backup_finish_date desc'

insert #backupfile exec(@exec)
set @physicalname=(select top 1 filename from #backupfile)
drop table #backupfile

if @physicalname is null 
begin
raiserror('No backups available for the specified database',1,1)
goto finish
end

--Checks to see if the location of the backup is on a physical drive or a network share and sets the variable accordingly

if left(@physicalname, 1) between 'a' and 'z' set @unc='\\' + @sourceserver + '\' + left(@physicalname, 1) + '$' + right(@physicalname, len(@physicalname)-2)
if left(@physicalname, 1) ='\' set @unc=@physicalname
if @unc is null 
begin
raiserror('Backupfile is not a file on a physical disk',1,1)
goto finish
end

--Restore the last backup onto the destination database (local server)
create table #backupdetails
(
LogicalName nvarchar(255),
PhysicalName nvarchar(255),
Type nvarchar(1),
FileGroupName nvarchar(255),
Size bigint,
MaxSize bigint
)

set @exec='restore filelistonly from disk=''' + @unc + ''''
insert #backupdetails exec(@exec)
set @datalogical=(select logicalname from #backupdetails where type='d')
set @loglogical=(select logicalname from #backupdetails where type='l')
drop table #backupdetails

declare @logphysical as nvarchar(255)
declare @dataphysical as nvarchar(255)

create table #destdetails
(
filetype char(1),
physicalname nvarchar(255)
)
set @exec='select top 1 ''l'', filename from [' + @destinationdb + '].dbo.sysfiles where status & 0x40=0x40'
insert #destdetails exec(@exec)
set @logphysical=(select physicalname from #destdetails where filetype='l')
truncate table #destdetails
set @exec='select top 1 ''d'', filename from [' + @destinationdb + '].dbo.sysfiles where status & 0x40<>0x40'
insert #destdetails exec(@exec)
set @dataphysical=(select physicalname from #destdetails where filetype='d')
drop table #destdetails




--Kill all users in Destination Database except system users

while (select count(*) from sysprocesses pc
inner join sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame<>'sa' and pc.loginame<>@systemaccount and pc.spid<>@@SPID and sd.name=@destinationdb)<>0
BEGIN
set @spid=(select top 1 pc.spid from sysprocesses pc
inner join sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame<>'sa' and pc.loginame<>@systemaccount and pc.spid<>@@SPID and sd.name=@destinationdb)
set @killcommand='kill ' + @spid
--print 'Killing ' + @spid
exec(@killcommand)
END


--Restore Database
RESTORE DATABASE @destinationdb
   FROM DISK = @unc
WITH
REPLACE,RECOVERY,
MOVE @datalogical TO @dataphysical, 
MOVE @loglogical TO @logphysical

finish: