Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
retrieve standby file and path from transaction log
/* ***************************************************************************************
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment