retrieve standby file and path from transaction log

standby_file_from_tlog.sql

/* *************************************************************************************** 
retrieve standby filepath from tlog 
credit for original idea is not mine, but I cannot find the original post any more 
*************************************************************************************** 
https://rileywaterhouse.co.uk/where-is-the-standby-file
*************************************************************************************** 
*/

DECLARE @database sysname;
SET @database = N'AW';
 
DECLARE @tlog NVARCHAR(260);
DECLARE @undo NVARCHAR(520);
DECLARE @cmd VARCHAR(1000);
 
SET @tlog =
(
     SELECT physical_name
     FROM sys.master_files
     WHERE database_id = DB_ID(@database)
     AND type = 1
);
 
CREATE TABLE #file (id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 line VARCHAR(255) NULL, wrap VARCHAR(510) NULL);
 
SET @cmd = 'sqlcmd -S' + @@servername + ' -Q"alter database ' + @database + ' set offline with rollback immediate"';
EXEC xp_cmdshell @cmd, no_output;
 
SET @cmd = 'type "' + @tlog + '"';
 
INSERT INTO #file (line)
EXEC xp_cmdshell @cmd;
 
SET @cmd = 'sqlcmd -S' + @@servername + ' -Q"alter database ' + @database + ' set online with rollback immediate"';
EXEC xp_cmdshell @cmd, no_output;

 

UPDATE here
SET wrap = REVERSE(ISNULL(there.line, '')) + REVERSE(here.line)
FROM #file AS here
LEFT JOIN #file there ON there.id = here.id + 1;

SELECT
  CONVERT(VARCHAR(260), REVERSE(REPLACE(filenames, LEFT(filenames, 1), '')))
   FROM (
                SELECT DISTINCT SUBSTRING(wrap, PATINDEX('%_[a-z]_[a-z]_[a-z]_._%_\_:_[a-z]_%', wrap), CHARINDEX(':', wrap,
 PATINDEX('%_[a-z]_[a-z]_[a-z]_._%_\_:_[a-z]_%', wrap)) - PATINDEX('%_[a-z]_[a-z]_[a-z]_._%_\_:_[a-z]_%', wrap) + 4) AS filenames
                FROM #file
                WHERE wrap LIKE '%_[a-z]_[a-z]_[a-z]_._%_\_:_[a-z]_%'
) a

DROP TABLE #file;