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

New Feature for SQL BAckup 6.4

astreetastreet Posts: 28
edited January 22, 2010 9:20AM 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.

Delay feature is currently available in SQL Sever 2005 log shipping.

Comments

  • Hi there,

    In the log shipping wizard, you can choose to offset the restore based on the transaction log backup. If you know how long the transaction log backup takes and how long on average it takes for the file to transfer across to the network share, you could adjust the offset to something like 240 mins if you wanted the restore to occur 4 hours after the intial backup was taken. This is on step 5 of the wizard.

    Does this sort of suit your needs?

    Pete
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • 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
  • Hi there,

    I have now raised this as a feature request for you under tracking number SB-4486. This will be considered for a future release of SQL Backup, however I cannot at this time confirm whether it will make it into the tool or not.

    Thanks for your suggestion though!

    Pete
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
Sign In or Register to comment.