Scripted Restore Using xp_DirTree For Transient Logical BAK File Name SQL Server -

hi trying restore db 1 server logical name of .bak file changes daily new timestamp, have far found success in determining name using following sql script provided jeff moden here: http://www.sqlservercentral.com/forums/topic1200360-391-1.aspx

            --===== create holding table file names              create table #file                     (                     filename    sysname,                     depth       tinyint,                     isfile      tinyint                     )             ;             --===== capture names in desired directory                  -- (change "c:\temp" directory of choice)              insert #file                     (filename, depth, isfile)              exec xp_dirtree '\\filepath\',1,1             ;             --===== find latest file using "constant" characters                  -- in file name , iso style date.              select top 1                      filename                #file               isfile = 1                 , filename '%.bak' escape '_'               order filename desc             ;             drop table #file 

my question how use basis of scripted restore operation? appreciated!

i have found success extending above cache directory path , ordered bak files chronologically determine use, combined restore operation, move logs.

--==check if db exists if drop use [master] if exists(select * sys.databases name='insert db name') drop database [insert db name]  --==start restore process declare @filename varchar(255), @pathtobackup varchar(255), @restorefilepath varchar(1000) declare @files table (subdirectory varchar(255), depth int, [file] int) set nocount on  --==set filepath set @pathtobackup = '\\insert path up'   --insert memory table using dirtree @ single file level  insert @files  exec master.dbo.xp_dirtree @pathtobackup,1,1      select top 1          @filename = [subdirectory]               @files              -- file         [file] = 1      ,       --==find logical name of bak file chronilogically ordered list            subdirectory '%.bak'      order         -- order descending newest file first naming convention      subdirectory desc  if left(reverse(@pathtobackup), 1) != '\' begin     set @pathtobackup = @pathtobackup + '\' end set @restorefilepath = @pathtobackup + @filename  --grab file path restore select @restorefilepath  --begin restore designated server restore database [insert name of database restore]  disk = @restorefilepath file = 1, --create transactional log files on target move 'mdf_file_name' 'file_path\file.mdf', move 'log_file_name' 'file_path\file.ldf', replace;