.net - TSQL - how to read datetimes from files with xp_dirtree -

i'm working tsql-script uses xp_dirtree pick file name directory multiple bak-files, , restores database using selected filename , directory.

however, want able select recent file said folder. currently, script picks top after ordering filename.

the folder might this:

------------ file1.bak  file2.bak  file3.bak  file4.bak ------------ 

my script looks this:

        declare @dir varchar(60)         set @dir = 'c:\testfolder\'          create table #directorytree (                id int identity(1,1)               ,subdirectory nvarchar(512)               ,depth int               ,isfile bit);          insert #directorytree (subdirectory,depth,isfile)         exec master.sys.xp_dirtree @dir,1,1;          declare @file varchar(60) =              (select top 1 subdirectory             #directorytree             isfile = 1 , right(subdirectory,4) = '.bak'             order subdirectory desc)          set @dir = @dir+@file         print '--selected file: "'+@dir+'"'         go         drop table #directorytree          --restores file         restore database testdb         disk = @dir         replace, file = 1 

dirtree not return information. xp_cmdshell , has enabled on instance dba may, or may not, allow. if here solution:

create table #files (name varchar(500))  insert #files exec xp_cmdshell 'dir *.exe';      -- find name after list space in string.... select name, reverse(substring(reverse(name),0,charindex(' ',reverse(name))))  #files       -- dates start numeric --check assumption carefully...      isnumeric(left(name,1))=1         --order date desc       --     order cast(left(name,17) datetime) desc