jimmy8ball jimmy8ball - 3 months ago 18
SQL Question

Scripted Restore Using xp_DirTree For Transient Logical BAK File Name SQL Server

Hi I am trying to restore a DB from one server to another where the logical name of the .bak file changes daily with a new timestamp, I have so far found success in determining this name using the following SQL script provided by Jeff Moden here: http://www.sqlservercentral.com/Forums/Topic1200360-391-1.aspx

--===== Create a holding table for the file names
CREATE TABLE #File
(
FileName SYSNAME,
Depth TINYINT,
IsFile TINYINT
)
;
--===== Capture the names in the desired directory
-- (Change "C:\Temp" to the directory of your choice)
INSERT INTO #File
(FileName, Depth, IsFile)
EXEC xp_DirTree '\\filepath\',1,1
;
--===== Find the latest file using the "constant" characters
-- in the file name and the ISO style date.
SELECT TOP 1
FileName
FROM #File
WHERE IsFile = 1
AND FileName LIKE '%.bak' ESCAPE '_'
ORDER BY FileName DESC
;
DROP TABLE #File


My question is now how do I use this as the basis of a scripted restore operation? any help would be very much appreciated!

Answer

I have found success by extending the above to cache the directory path and ordered bak files chronologically to determine which to use, then combined the restore operation, with move for logs.

--==CHECK IF DB EXISTS IF IT DOES DROP IT
USE [master]
IF EXISTS(SELECT * FROM sys.databases where name='insert db name')
DROP DATABASE [insert db name]

--==START THE RESTORE PROCESS
DECLARE @FileName varchar(255), @PathToBackup varchar(255), @RestoreFilePath varchar(1000)
DECLARE @Files TABLE (subdirectory varchar(255), depth int, [file] int)
SET NOCOUNT ON

--==SET THE FILEPATH
SET @PathToBackup = '\\insert path to back up'

 --insert into memory table using dirtree at a single file level
 INSERT INTO @Files
 EXEC master.dbo.xp_DirTree @PathToBackup,1,1
     SELECT TOP 1 
        @FileName = [subdirectory]
     FROM 
        @Files
     WHERE
        -- get where it is a file
        [file] = 1
     AND 
     --==FIND THE LOGICAL NAME OF THE BAK FILE FROM THE CHRONILOGICALLY ORDERED LIST   
        subdirectory LIKE '%.bak'
     ORDER BY
        -- order descending so newest file will be first by naming convention
     subdirectory DESC

IF LEFT(REVERSE(@PathToBackup), 1) != '\'
BEGIN
    SET @PathToBackup = @PathToBackup + '\'
END
SET @RestoreFilePath = @PathToBackup + @FileName

--Grab the file path to restore from
SELECT @RestoreFilePath

--BEGIN THE RESTORE TO THE DESIGNATED SERVER
RESTORE DATABASE [insert name of database to restore] 
FROM DISK = @RestoreFilePath
WITH
FILE = 1,
--Create transactional log files on target
MOVE 'mdf_file_name' TO 'file_path\file.mdf',
MOVE 'log_file_name' TO 'file_path\file.ldf', REPLACE;