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 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.
SET QUOTED_IDENTIFIER OFF 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:\MSSQL\Data\Data.mdf", MOVE "LogFile" TO "D:\MSSQL\Data\log.ldf", NORECOVERY, REPLACE, STATS = 10 GO')