If you are using log shipping to keep a warm copy of a database ready for anything, or even using it to offload reporting, it is common to use the WITH STANDBY option of the RESTORE statement. By specifying this option you can bring a log-shipped database online in a read-only state, that you can then issue queries against.
RESTORE with STANDBY
The recovery process in the RESTORE reads the log file and brings the database online in a transactionally consistent state by undoing transactions that are not committed. Doing this however, breaks the log chain and would prevent you from applying any further transaction logs. By using the STANDBY option, SQL keeps a record of what was undone, allowing the database to come online, and at a later point if you want to apply more transaction logs, then the undone transactions can be be reverted back to how they really were, and you are back at the correct point in the log chain.
To specify the STANDBY option you need to provide the location of a standby file that SQL will use to store the results of this undoing
RESTORE DATABASE AW
FROM DISK = N'F:\temp\AW2019.bak'
WITH FILE = 1,
move N'AdventureWorks2017' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\AW_LS.mdf',
move N'AdventureWorks2017_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\AW_LS.LDF',
standby = N'D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\AW_LS_rollback.BAK', nounload, stats = 3;
As with most files for SQL Server, there is no limitation on what the standby file extension has to be. Some people have adopted a TUF (Transaction Undo File) standard, which I think is what the in-built log shipping will specify, but it makes no real difference.
Now you see me ….
However, once you have specified this standby file, it can be difficult in finding out what the file is called, and where it is on your disk. There is no property of a database that exposes this information, and nothing in any of the regular DMVs that you might think would offer this up. There is a very good reason for this. You don’t really need to know where that standby file is. SQL needs to know so that it can revert the changes and apply further log files (and as it does it will remove the standby file), but there’s nothing as a user that you can do with that file.
The documentation is explicit though:
The standby_file_name specifies a standby file whose location is stored in the log of the database.
But what if you really want to know the name and location.
Well that’s where I found myself. A client’s log-shipped database went offline due to a disk error and I was trying to rebuild the files. All in an attempt to avoid having to restore from scratch. I knew where the mdf and ldf files needed to be. But I had no clue as to where the standby file had been living.
An internet search yielded a single answer, but unfortunately the script had some issues and did not work properly (and I’m not going to link it here as the url is now dead), but it did give me a starting point.
A word of warning
Never copy code from the internet and blindly run it on your production database. Or any database for that matter, where you are paid to look after it!
Please understand the script first.
It does need to use xp_cmdshell. It will take the database you are querying offline. If either of those things are going to cost you your job, don’t do it!
/* *********************************** */ /* retrieve standby filepath from tlog */ /* credit for original idea is not mine, but I cannot find the original post any more */ /* *********************************** */ 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;
What does it do?
Basically it dumps the contents of the log file as text into a temp table (#file). To do that, the database needs to be offline, otherwise access to the log file will be blocked. After manipulating some of the data around, it simply looks for a pattern in text that matches a 3-letter file extension. It can take a while to execute depending on the size of your log file.
D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\AW_LS_rollback.BAK
(1 row affected)
You may get multiple results. There may be false-positives. But there should be enough in the output to give you a clue as to where the standby file is.
You’ll probably never have the need to use this, and me neither (again)!