SQL Server xp_dirtree – Parse Directory for File names

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')

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s