In SQL server, How to see the standby file path when we restore the database in standby mode:

 

I was answering to the questions on Social.msdn.microsoft.com forum site, Someone posted the question :

 How to see the standby file path when we restore the database in standby mode:

 I checked lots of DMVs e.g. msdb..restorefile, msdb..restorehistory etc. but couldn't find any place to get the standby file name. I Spent around 1-2 hours to find out the path. The answer, really awesome, was provided by someone else on the forums. I decided to blog this so that in future if someone wants to know about it, it's easier to find the answer.

 

Just a little background for what happens when we restore the database in standby mode:

When we restore the database with recovery, it rolls back all the uncommitted transactions and bring the database online. And, when we restore the database with standby it leaves the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted.

 

 Here is how we can find the name of the .tuf,.Bak file which is created when we restore database in standby mode.

 

** Please create a folder temp in C drive.

 

** Below script create the database,stop and read the log file to find the tuf filepath.

 

/* create test database */

CREATE DATABASE [DELETEME] ON

                PRIMARY (NAME = N'DELETEME_data', FILENAME = N'C:\Temp\DELETEME_data.mdf')

                LOG ON (NAME = N'DELETEME_log', FILENAME = N'C:\Temp\DELETEME_log.ldf');

GO

BACKUP DATABASE [DELETEME] TO DISK = N'C:\Temp\deleteme.bak';

GO

BACKUP LOG [DELETEME] TO DISK = N'C:\Temp\deleteme.trn' WITH NORECOVERY;

GO

RESTORE DATABASE [DELETEME] FROM DISK = N'C:\Temp\deleteme.bak' WITH STANDBY = N'C:\Temp\DELETEME.TUF';

GO

/* run script to find undo file location

/* then run this cleanup */

DROP DATABASE [DELETEME];

EXEC xp_cmdshell 'DEL C:\Temp\deleteme.*', no_output

*/

 

 

/* ******************************** */

/* retrieve undo filepath from tlog */

/* ******************************** */

DECLARE @DATABASE SYSNAME;

SET @DATABASE = 'DELETEME';

 

 

declare @tlog varchar(260);

declare @undo varchar(520);

declare @cmd varchar(1000);

 

set @tlog =

(

                select

                                sys.sysaltfiles.filename

                from

                                sys.sysaltfiles

                left join

                                sys.sysdatabases

                                on sys.sysdatabases.dbid = sys.sysaltfiles.dbid

                                and sys.sysdatabases.filename = sys.sysaltfiles.filename

                where

                                sys.sysaltfiles.dbid = db_id(@DATABASE)

                and

                                sys.sysdatabases.filename is null

);

 

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 = here.line + isnull(there.line, '')

from #file as here

left join #file there on there.id = here.id + 1;

 

set @undo = (

                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) + 8)

                from #file

                where wrap like '%_[a-z]_:_\_%_._[a-z]_[a-z]_[a-z]_%'

);

 

drop table #file;

 

select @undo = replace(@undo, LEFT(@undo, 1), '');

select convert(varchar(260), @undo) as undo_filename

 

 You will get the output as the .tuf filename along with path.

 

Kind regards

Harsh Chawla

Comments

• Anonymous
  May 01, 2015
  The comment has been removed