New Feature for SQL BAckup 6.4

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.

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

peter.peart

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

astreet

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

peter.peart

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

astreet

thanks