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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[former_member188958]

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/CJAJHJB...

Cheers,
-bret

[Former Member]

Hi Bret

Thanks for the response... I'm assuming the history is enabled as running the "load database <SID> with listonly = 'LOAD_SQL'" commands brings back the complete list/history of backups to restore. Although I can't see the -m startup parameter nor a dumphist file in $SYBASE so not entirely sure...

But yes I suppose it shouldn't be too hard to work out up until which file we would need; just though there might be a less manual way of doing it / that ASE having the history, would be able to work it out itself. Perhaps not then!

Thanks
Ross

[former_member188958]

Hi Ross,

Turns out that the "until_time" syntax for LOAD DATABASE works, but in a different way than the option for LOAD TRAN. It isn't used to directly cause the database to be loaded and recovered to a point in time. For LOAD DATABASE, it is a sub-option for the "with listonly = 'LOAD_SQL'" option to generate the appropriate sequence of commands to recover the database to a point in time: Example:

1> load database test with listonly = 'LOAD_SQL', until_time = "Dec 9, 2016 6:35:00:000am"
2> go
LOAD DATABASE test FROM 'test.dmp'
go
LOAD TRAN test FROM 'test.trn1'
go
LOAD TRAN test FROM 'test.trn2'
go
LOAD TRAN test FROM 'test.trn1'
WITH UNTIL_TIME = 'Dec 9 2016 6:35:00:000AM'
go

If you try to specify a time that precedes the database dump, you get warning like this:

1> load database test with listonly = 'LOAD_SQL', until_time = "Dec 8, 2016 6:35:00:000am"
2> go
LOAD DATABASE test FROM 'test.dmp'
go
Warning. Unable to generate the load sequence until 'Dec 8 2016 6:35:00:000AM'
because the database dump that meets the search criteria also contains
transactions from 'Dec 8 2016 6:35:00:000AM' until 'Dec 9 2016
6:22:45:180AM'.

Cheers,

Bret Halford
SAP Product Support