SQL Backup Restore logshipping delay

• 
  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)

  jerryhung January 13, 2010 16:57

  - 0 +
• 

  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 
  

  astreet February 03, 2010 10:27

  - 0 +
• 
  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

  jerryhung February 03, 2010 10:55

  - 0 +
• 
  Excellent idea!
  
  Thanks!

  astreet February 03, 2010 11:01

  - 0 +
• 
  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.

  ddubick May 27, 2010 12:55

  - 0 +

Add comment

Please sign in to leave a comment.