Log Shipping TUF File Access is Denied (4224961)

Log Shipping TUF File Access is Denied

During startup of warm standby database 'DatabaseName_Log' (database ID 11), its standby file ('\\UNC_path\_Shippedlogs\9330EE4B-A9E6-40DE-B8CF-DatabaseName_Log_20130809145754.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the root operating system error, correct the problem, and retry startup.

TUF file location must be the same location as the .TRN files

NOTE:  This is a function of SQL Server, however the symptoms may appear as an error message in LiteSpeed Log Shipping.

Explanation:

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.

The TUF file is the undo file that is needed to keep the secondary server in the read-only state. If you used the norecovery state instead, you wouldn't have that file on the secondary server. the .tuf file is created when we restore database in standby mode.

Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recorded to the undo file and only commited transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

The tuf file path is listed in the column backup_destination_directory in log_shipping_secondary on the primary server.

The last restored file path details can be found on dbo.log_shipping_monitor_secondary , dbo.log_shipping_secondary_databases tables.

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

/* retrieve undo filepath from tlog
 */Courtesy of Harsh Chawla: http://blogs.msdn.com/b/batala/archive/2011/07/21/how-to-see-the-standby-file-path-when-we-restore-the-database-in-standby-mode.aspx

/* ******************************** */
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.

*************************************************************************
If the transaction logs are in a NETAPP filer backup directory, use the FILECOPY job to copy them from there to the local  drive on the remote server (subscriber).  That is where the TUF file and transaction logs are accessed and restored from.

You cannot restore from the NETAPP filer backup directory directly, (it fails with MS SQL 2008 but works with MS SQL 2005).  Use the FILECOPY from for MS SQL2008 Log Shipping jobs and back up the tlogs to the NETAPP filer and copy them to the remote server local drive to restore them and allow the TUF file to be accessed locally.

CR52559

The transaction undo file contains modifications that were not committed on the source database but were in progress when the transaction log was backed up AND when the log was restored to another database, you left the database in a state that allowed addition transaction log backups to be restored to it (at some point in the future. When another transaction log is restored, SQL Server uses data from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are were completed in the next transaction log file). Following the restore, the undo file will be re-written with any transactions that, at that point, are incomplete.