SQL Backup Restore logshipping delay

astreet

In our standby database environment, we backup transactions on the production database and ship them to the standby database without immediately applying the logs to the standby. We want to be able to apply all un-applied transactions up until a specific time. At all times, we want our standby database to be at least 4 hours behind production. How can we do that with SQL Backup?

jerryhung

Interesting, I wouldn't know the easiest way to do a X-hour behind restore either, unless you add in some "time check" logic for LOG_*.sqb files

In our case, we Network Copy all log files off production to the file server
but only run the "Log Restore" job from 10pm-4am on the standby server (low activity times)

astreet

Posted: Wed Jan 20, 2010 8:05 pm    Post subject:     

--------------------------------------------------------------------------------
 
Hi Pete, 

I don't this will work because Redgate will still processes all log files present in the log directory regardless of the log file timestamp. 

What we have done is delay processing of individual log files by 4 hrs based on log file timestamp. 

Ex. 
We log backup our production database every 30 minutes and copy to standby. Our log ship job runs every hour and only processes the individual log files which are over 4 hrs. We have a custom script which looks at the individual log files in the log directory and grabs files based on filename timestamp to process. 

ex. script 

Declare int 

Set @DelayMins = 240 

create table #dir 
( 
[FileName] varchar(2000) 
) 
select @cmd = 'dir /B ' + @SourcePath + '*.sqb' 

insert #dir exec master..xp_cmdshell @cmd 

-- Delete anything which is not a log backup for this database 
delete #dir 
where [FileName] is null 
or [FileName] not like '%.sqb' 
or [FileName] not like '%^_log^_%' escape '^' 
or [FileName] not like @DBName + '^_log^_%' escape '^' 

-- Delete anything that is less than @DelayMins minutes old 
delete #dir 
where [FileName] > @LastFile 

while exists (select * from #dir) 
begin 
select top 1 @filename = [FileName] 
from #dir d 
order by right([FileName],20) 

if not exists (select 1 from #restores where [FileName] = @filename) 
begin 
print convert(varchar(25),getdate(),121) + ' Restoring ' + @DBName + ' from ' + @filename 

set @StandByFile = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\UNDO_' + @DBName 

-- Restore log 
select @cmd = 'master..sqlbackup ' 
+ ' -SQL restore log ' 
+ @DBName 
+ ' from disk = ''' 
+ @SourcePath 
+ @filename 
+ ''' with standby = ''' 
+ @StandByFile + '''' 

exec(@cmd) 

end 

jerryhung

Most DBAs do not like to enable the xp_cmdshell, so it'll make the above solution not applicable

An alternative is to use
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

astreet

Excellent idea!

Thanks!

ddubick

Did you ever get a working solution for this?

I currently use Quest LiteSpeed (which has this feature) and am contemplating a switch to SQL Backup, but the loss of this feature might affect my decision.