[Backups] Restoring Litespeed archival backups to secondary server

After a year or so, databases on my primary server get moved to a secondary server. I wrote the below script to look in a particular for 1 backup, then it determines where to restore it based off the database name. Hopefully someone else can use this.

DECLARE @backup_directory VARCHAR(500), @Verified VARCHAR(100), @full_backup_name VARCHAR(600),
  @database_name sysname, @restore_directory VARCHAR(500), 
  @logical_name_log  VARCHAR(50), @physical_name_log  VARCHAR(50),
  @logical_name_data VARCHAR(50), @physical_name_data VARCHAR(50)
  ,@with_data VARCHAR(200), @with_log VARCHAR(200)


---------------------------------------------------------------------
--put the name of the folder with the backup you want restored here--
---------------------------------------------------------------------
SET @backup_directory = '\\servername\share\databasefolderbackup'


IF RIGHT(@backup_directory, 1) <> '\'
 SET @backup_directory = @backup_directory + '\'

IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'tempdb.[dbo].[#listing]') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
 )
DROP TABLE #Listing
create table #Listing
(resultant nvarchar (255))

--Find the backups in the given folder
declare @dirlist varchar(500)
select @dirlist = 'exec master..xp_cmdshell ''dir /b "'+  @backup_directory + '*"'''
insert #Listing exec (@dirlist)

--get the most recent 
select @Verified = MAX(resultant) from #listing WHERE resultant LIKE '%.bak'
DROP TABLE #Listing

--get a list of all logical files within the backup so we can restore with the right data
SELECT @full_backup_name = @backup_directory + @Verified

if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

CREATE TABLE #database_details 
 (
  LogicalName sysname, 
  PhysicalName varchar(500), 
  [TYPE] VARCHAR(2),
  FileGroupName varchar(50),
  [Size] VARCHAR(50),
  [MaxSize] VARCHAR(50))
INSERT INTO #database_details
 EXEC MASTER..xp_restore_filelistonly @filename = @full_backup_name
 
--get the names of everything.
SELECT @logical_name_data = LogicalName, @physical_name_data = PhysicalName
 FROM #database_details WHERE [TYPE] = 'D'
SELECT @logical_name_log = LogicalName, @physical_name_log = PhysicalName 
 FROM #database_details WHERE [TYPE] = 'L'

SELECT @database_name = @logical_name_data

--set up the folders where the restore will automatically go. 
SELECT @physical_name_data = CASE 
 WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009\'
 WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008\' 
 ELSE 'not known'
 END 
 + right(@physical_name_data, CHARINDEX('\', reverse(@physical_name_data))-1)

SELECT @physical_name_log = CASE 
 WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009_Logs\'
 WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008_Logs\' 
 ELSE 'not known'
 END 
 + right(@physical_name_log, CHARINDEX('\', reverse(@physical_name_log))-1)

--need to set these separately since you can't call within the SP
SELECT @with_data = 'MOVE "' + @logical_name_data + '" TO "' + @physical_name_data + '"'
SELECT @with_log = 'MOVE "' + @logical_name_log  + '" TO "' + @physical_name_log  + '"'

--Now do a restore with MOVE.  
exec master..xp_restore_database @database=@database_name
  , @filename= @full_backup_name
  , @with = @with_data
  , @with = @with_log