SQL Server xp_dirtree

I needed a way to parse the filenames in a directory and plug them into a RESTORE command. The xp_dirtree works really well for this.

IF OBJECT_ID('tempdb..#restoreFiles') IS NOT NULL
DROP TABLE #restoreFiles

SET QUOTED_IDENTIFIER OFF
CREATE TABLE #restoreFiles
(
[filename] VARCHAR(2000),
[depth] INT,
[file] INT
)

INSERT INTO #restoreFiles
EXECUTE xp_dirtree 'D:backups',1,1

From there I was able to set a variable based off a WHERE clause. My FULL backups are pre-pended with the word FULL so I can plug that into my RESTORE command.

DECLARE @FullBackupFile VARCHAR(2000) = (SELECT TOP 1 [filename] FROM #restoreFiles WHERE [filename] like 'FULL%' ORDER BY [filename] DESC)

PRINT ('RESTORE DATABASE DbName
FROM DISK = "D:Backups' +  @FullBackupFile + '" 
WITH MOVE "DataFile" TO "D:MSSQLDataData.mdf",
MOVE "LogFile" TO "D:MSSQLDatalog.ldf",
NORECOVERY,
REPLACE,
STATS = 10
GO')