Skip to Content

Can I use 'until_time' with 'load database' for point in time recovery?

Hi

Is there a simple way to identify which trans log dumps are required in order to restore to a specific point of time (Sybase ASE 16), or run just one or two load commands to bring back all the necessary logs?

For example, there may have been 50 trans log dumps since the last backup. A user makes a critical error and we need to restore to before that time. We can run the following command to show all trans logs needed up to the current time:

load database <SID> with listonly = 'LOAD_SQL'

But that shows us the last backup plus ALL the trans logs since - we might only need, say, 48 of the trans log dumps. We can use the 'load transaction from' and 'with until_time' command on trans log backup number 48, but how do we determine that it's that particular trans log that contains transactions within that time frame?

Sure, we could do a 'load trans' 'with headeronly' to read the header of a log to check it's time frame but we need to estimate which trans log file is correct (i.e. guess with 46, with 47 etc) and we still end up with a lot of load commands (48 of them!).

Is there not some way to use 'until_time' with the load database command, for example:

load database <SID> with until_time = "<time>"

That doesn't work... even if we try to put in a 'from' with the location of a valid database dump file on disk, it still doesn't like it. The documentation seems to suggest that 'until_time' can be used with 'load database' (as opposed to 'load transaction') but I can't find any examples and cannot get it to accept the command.

Has anyone been able to successfully use 'until_time' with 'load database'?

Alternatively, we could manually restore the database with the 'load database'; is it then possible to run ONE 'load tran' command pointing at the trans log backup area and using the 'until_time' command, i.e.

load tran <SID> from '<path to all trans logs>' with until_time = "<time>"

i.e. rather than specifiy each and every dump file up until the last. Is it possible to just point at the path where all the log files are? Doesn't seem to allow it.

Just trying to simplify a point in time restore should we have many transaction logs to restore...

Thanks
Ross

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Dec 08, 2016 at 04:12 PM

    Hi Ross,

    "until_time" cannot be used with LOAD DATABASE.

    It is fairly common practice to incorporate date and time information into the name of dumps.
    If you do that, you can usually tell which was the first tran dump to be done after an arbitrary point in time, or at least narrow it down to 2 possibilities.

    The new dump history file feature can also be used to determine which was the first tran dump done after a point in time.

    http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1572/html/new_features/CJAJHJBB.htm

    Cheers,
    -bret

    Add comment
    10|10000 characters needed characters exceeded

    • That's *fricken* neat!

      'course, would've helped had I paid closer attention to the documentation for load database:

      until_time = <datetime>
            generates a load sequence to load (to a specified point in time).

      With emphasis on the "generates a load sequence".

  • Dec 08, 2016 at 04:42 PM

    NOTE: Tests run against ASE 15.7 SP138 and ASE 16.0 SP02 PL04

    load database/until_time errors out for me in both test dataservers, so obviously a documentation error. Someone from SAP would need to figure out if this was just a typo or perhaps a feature that was planned but never implemented.

    ---------------

    It'd be nice if you could just supply the until_time clause for a series of load transaction commands and have ASE load all log files (in correct load sequence, of course) up until the desired time, but at the moment this generates errors if the log file doesn't encompass the time stamp provided by the until_time clause. Net result being the operator has to know which log file can have the until_time clause applied to it.

    Might be a good idea for future releases?

    ---------------

    If you've enabled the dump history file feature you could use load database/with listonly=load_sql to generate the set of load database/transaction commands, then edit said command list to load your database up to the desired point in time.

    Otherwise I'm not aware of any method of automatically loading a set of log dump files from a directory/path

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mark

      Yes looking like a documentation error/something that wasn't ever implemented perhaps... yes perhaps newer releases will incorporate this

      Seems we can get the details using listonly and work it out without too much trouble, just surprised that there's not a more automated way.

      Cheers

      Ross

  • Dec 13, 2016 at 10:29 AM

    Thanks guys - tried this myself this morning - seemed to work well!

    Made full set of backups:

    /usr/sap/archlog/PROD/SDP/master.DB.20161213.090344.000.
    /usr/sap/archlog/PROD/SDP/SDP.DB.20161213.090611.000.
    /usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.090936.000.
    Dec 13 2016 9:10:33:150AM

    Waited a while and made another trans dump:
    /usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.092135.000.
    Dec 13 2016 9:21:46:846AM

    'Incorrect change' made; carried out a trans dump after:
    /usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.092322.000.
    Dec 13 2016 9:23:41:220AM

    Opted to restore a minute before the 2nd trans dump (a couple of minutes before the 'mistake'); ran the desired 'listonly' and 'until_time' command as Bret specified:

    load database SDP with listonly = 'LOAD_SQL', until_time = "Dec 13 2016 9:20:46:846AM"
    go

    The output is:

    LOAD DATABASE SDP FROM '/usr/sap/archlog/PROD/SDP/SDP.DB.20161213.090611.000'
    go
    LOAD TRAN SDP FROM '/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.090936.000'
    go
    LOAD TRAN SDP FROM '/usr/sap/archlog/PROD/SDP/SDP.TRAN.20161213.092135.000' WITH UNTIL_TIME = 'Dec 13 2016 9:20:46:846AM'
    go

    This looks to be correct!

    Ran successfully too!

    We're running:
    SP2 patch level 4 Hotfix 1

    Cheers
    Ross

    Add comment
    10|10000 characters needed characters exceeded