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.

asked 09 Jan, 04:47

ThelmaCottage's gravatar image

ThelmaCottage
141111320
accept rate: 0%

edited 09 Jan, 07:44

Breck%20Carter's gravatar image

Breck Carter
30.1k490672977


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

permanent link

answered 09 Jan, 08:21

Reg%20Domaratzki's gravatar image

Reg Domaratzki
6.6k337101
accept rate: 39%

edited 09 Jan, 08:56

1

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?

(09 Jan, 08:33) Volker Barth
Replies hidden

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

(09 Jan, 08:57) Reg Domaratzki

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

(09 Jan, 11:33) ThelmaCottage
Replies hidden
1

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

(09 Jan, 11:59) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×82
×52
×42
×7

question asked: 09 Jan, 04:47

question was seen: 145 times

last updated: 09 Jan, 11:59