New backup plan

[Former Member]

We currently run a FULL backup of our consolidated database every night (involved in REPLICATION)

backup database directory 'f:\\sdata_archive' transaction log rename

The database is quite large (360GB) and takes about 3 hours to back up.

I have been looking at performing 1 FULL backup a week (Sunday) and then incremental backup's between (Mon-Sat).

I've been looking at the online DOCX and see there are several ways to run a incremental backup - but I'm not sure which one to use - do I truncate, rename or neither of these ?

BACKUP DATABASE DIRECTORY 'c:\\\\temp\\\\SQLAnybackup' TRANSACTION LOG ONLY;
BACKUP DATABASE DIRECTORY 'c:\\\\temp\\\\SQLAnybackup' TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE;
BACKUP DATABASE DIRECTORY 'c:\\\\temp\\\\SQLAnybackup' TRANSACTION LOG ONLY TRANSACTION LOG RENAME;

Any suggestions would be welcome.

[regdomaratzki]

You never truncate a transaction log involved in replication or synchronization. IMHO, a variation of the third option is likely the one you want.

BACKUP DATABASE DIRECTORY 'c:\\temp\\SQLAnybackup' TRANSACTION LOG ONLY TRANSACTION LOG RENAME MATCH;

When you use the TRANSACTION LOG RENAME MATCH option, you end up with two copies of the backed up transaction log, whose name is based on the current date (200109AA.log for example). One copy is in the c:\\temp\\SQLAnybackup directory and one is in the same directory where the active transaction log exists. These two copies have very distinct uses :

1. The log files in c:\\temp\\SQLAnybackup are used if you ever need to perform recovery.
2. The log files in the same directory where the active transaction log exists are used by dbremote/dbmlsync to scan and send messages. dbremote/dbmlsync can automatically delete logs in this directory once it determines they are no longer needed.

You should NEVER let dbremote/dbmlsync scan the transaction logs in the backup directory, since they may delete logs that are no longer needed for replication, but may still be needed for recovery.

Two more comments :

1. Having a Backup Plan is useless if you do not also have a recovery plan. You need to test your recovery plan.
2. DCX documentation is no longer being updated. The most up to date version can be found at https://help.sap.com/viewer/product/SAP_SQL_Anywhere/17.0/en-US.

Reg

[VolkerBarth]

Hm, the link gives me a "Sorry, we can't find the page you're looking for" SQL Help Portal response, even if I omit the trailing point. The wellknown URL https://help.sap.com/viewer/product/SAP_SQL_Anywhere/17.0/en-US does work as expected...

Is draft documentation publically available?

[regdomaratzki]

Pasted the wrong link. Updated to a customer-viewable link.

[Former Member]

Great, thanks - yes my DCX did not have that combination using MATCH

[VolkerBarth]

Well, what version do you use:

The MATCH option is not a new feature at all and certainly also documented in DCX for 11.0.1 and above...