STOPAT with LATEST_ALL

meastland

Peter,

Are there any plans to support the STOPAT parameter with the LATEST_ALL restore option in a future version of SQL Backup? If so, would it be possible to get an ETA?

Thanks,

-Mike

Linda Hawksworth

This is actually a bug. It is logged in our tracking system with reference SB-4689. It has been pencilled in to be fixed in SQL Backup 7.0 which is due for release in Q4.

meastland

Linda,

Will there be a patch back-ported for 6.x? My company did not purchase the support agreement, so I think we'd have to pay for 7.0.

Thanks,

-Mike

petey

You can download a patch from here:

ftp://support.red-gate.com/Patches/sql_ ... _5_2_4.zip

This patch allows you to use the STOPAT option with the LATEST_ALL option. It works the same way as when you restore multiple t-log backups using the RESTORE LOG syntax i.e. it simply passes the STOPAT value to SQL Server, and if it attempts to restore a t-log backup that starts after the STOPAT value, SQL error code 4305 is raised. To make it usable, you may need to catch that condition, and restore the database to a usable state manually e.g.

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs FROM DISK = [e:\backups\*.sqb] LATEST_ALL WITH STOPAT = [2011-06-27 11:20], NORECOVERY"', @errorcode OUTPUT, @sqlerrorcode OUTPUT
IF @sqlerrorcode = 4305
BEGIN
  RESTORE DATABASE pubs WITH RECOVERY
END

Please note that this patch has not been subjected to rigorous testing, and that you should only use this patch if you really need that functionality.

meastland

Peter,

I finally got around to testing the combination of LATEST_ALL with STOPAT. This is probably by design, but I noticed that the restore worked exactly as you indicated it would, as long as the STOPAT value was later than the most recent database-level backup. However, if I specify a STOPAT value earlier than the most recent database-level backup, the process will still restore the most recent FULL, followed by the most recent DIFFERENTIAL, and then it will fail when it attempts to apply the LOG backups. This behavior can be illustrated by the following example.

Assume I have the following available backups:

L-8
L-7
D-6
L-5
L-4
D-3
L-2
L-1
F-0

If I attempt to set the STOPAT parameter to a datetime value between L-4 and L-5, the LATEST_ALL parameter seems to still force the restore of F-0 followed by D-6 before processing the STOPAT cutoff time. As I said, this is probably by design. However, it might be beneficial for SQL Backup to be able to apply the STOPAT criteria before it selects which backup sets it is going to use.

Anyway, just thought I would share the results of my testing. Keep up the good work, it is much appreciated.

Regards,

-Mike Eastland

petey

Thank you for pointing that out. We have raised a bug report for this issue (SB-4702).