What are the challenges you face when working across database platforms? Take the survey
Options

STOPAT with LATEST_ALL

meastlandmeastland Posts: 47
edited July 19, 2011 8:32PM in SQL Backup Previous Versions
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

Comments

  • Options
    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.
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    Thank you for pointing that out. We have raised a bug report for this issue (SB-4702).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.