Competition: What’s your favorite Redgate tool? Enter now.

SQL Backup Restore logshipping delay

astreetastreet Posts: 28
edited May 27, 2010 12:55PM in SQL Backup Previous Versions
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?

Comments

  • 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)
    Jerry Hung
    DBA, MCITP
  • 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 
    
  • 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
    Jerry Hung
    DBA, MCITP
  • Excellent idea!

    Thanks!
  • 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.
Sign In or Register to comment.