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