When I first began this blog, back at the beginning of my DBA career, it was purely a place for me to keep queries, things I was learning, processes and handy links. This is one of those posts, there’s nothing new or ground-breaking here – just an elegant simple solution to a common problem.

The problem: I’m using a simple maintenance plan to take nightly backups of some production databases to a UNC path within our network. I want to create a job that will pick these backups up and restore them on to a test server. Either on demand or on a schedule. I’m keeping seven days backup in the UNC target and I want to be restoring the most recent backup.

The options:

1. Use the backup history tables in msdb to retrieve the filename and path of the most recent backup. We don’t allow TCP/IP traffic between our production and test networks so I can’t query the production backup history tables in msdb from the test server

2. Parse the date from the backup filename and use that. This was definitely a possibility, the SQL Server maintenance plans append the date to the filename by default. But that option felt a little clunky and error prone to me. What if I wanted to take an out of band backup manually – and get that picked up?

3. Use the date created property from the file system to choose the youngest backup. This was the solution I wanted.

The solution: I looked for a way of finding the most recent backup using DOS commands but I couldn’t find a good single statement solution. One of the sys admins at work supplied this powershell solution:

(Get-ChildItem -Path [UNC path] | Sort CreationTime -Descending | Select Name -First 1).Name

Calling this from SQL using xp_cmdshell:

exec xp_cmdshell 'powershell.exe -c "(Get-ChildItem -Path [UNC path] | Sort CreationTime -Descending | Select Name -First 1).Name"'

Executing the above command returns a two row result set. One null row and one with the filename and extension of the youngest file in the given location. From there it was a straight forward task to pipe the output into a table variable, get rid of the null row, and construct the restore statement.

USE [master]
ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE [database] SET MULTI_USER; 
DECLARE @files TABLE([file] VARCHAR(255))
DECLARE @filepath VARCHAR(256)
DECLARE @sql NVARCHAR(512)
INSERT @files
EXEC xp_cmdshell 'powershell.exe -c "(Get-ChildItem -Path UNC path | Sort CreationTime -Descending | Select Name -First 1).Name"'
DELETE @files WHERE [file] is null
SELECT TOP 1 @filepath = 'UNC path' + [file] FROM @files;
SELECT @sql = 'RESTORE DATABASE [database] 
FROM  DISK = ''' + @filepath + ''' 
WITH  FILE = 1,  
MOVE N''Database'' TO N''D:\Data\Database.mdf'', 
MOVE N''Database_log'' TO N''D:\Logs\Database_log.ldf'', 
NOUNLOAD,  REPLACE,  STATS = 5'
EXEC dbo.sp_executesql @sql