From Warm-Standby to normal Read-Only mode

use master

go

-- take the user database in single_user mode first

alter database [db_name] set single_user with rollback immediate;

--Check and Kill user sessions connected to that database

--Restoring the database

restore database [db_name] with recovery; 

It may fail if no .tuf is there for this database.

restore database [db_name] with recovery, continue_with_error;

 Please do a DBCC CHECKDB after this and see any error is seen. Think twice before put it in Production DB. 

--Reverting back to MULTI USER mode

alter database [db_name] set multi_user with rollback immediate;

--making the db READ ONLY

alter database [db_name] set read_only with no_wait;

TUF file stands for Transaction Undo File in SQL Server

In log shipping scenario if we choose “Standby” option for the secondary server then the transaction log backup file would be restored in standby mode. As discussed earlier, this would need a TUF file to be generated automatically. The location of this file is not configurable and it has been changed in few versions of SQL. You can find the location of the .tuf file through below query:

declare @enable int;
select @enable = convert(int, value_in_use) from sys.configurations where name = 'default trace enabled' 
print 'default trace is NOT enabled.'
if @enable = 1  
begin 
        declare @curr_tracefilename varchar(500);
        declare @base_tracefilename varchar(500);
        declare @status int; 
        declare @indx int;   
        declare @temp_trace table ( 
                Error int
        ,       StartTime datetime
        ,       HostName sysname collate database_default null
        ,       ApplicationName sysname collate database_default  null
        ,       LoginName sysname collate database_default null
        ,       Severity int
        ,       DatabaseName sysname collate database_default null
        ,       TextData nvarchar(max) collate database_default 
        ); 
        
        select @status=status, @curr_tracefilename=path from sys.traces where is_default = 1 ;
        set @curr_tracefilename = reverse(@curr_tracefilename) 
        select @indx  = patindex('%\%', @curr_tracefilename)  
        set @curr_tracefilename = reverse(@curr_tracefilename) 
        set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 

        
        select  StartTime
        ,        TextData  
        ,       HostName
        ,       ApplicationName
        ,       LoginName
        ,       DatabaseName    
        from ::fn_trace_gettable( @base_tracefilename, default ) 
        where EventClass = 115
        and TextData like '%Restore%Standby%' 
        and ServerName = @@servername
        order by StartTime

end