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.
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