Backing up SQL Remote systems

SQL Remote replication depends on access to operations in the transaction log, and access to old transaction logs. Any backup strategy that you implement must incorporate the maintenance of the transaction logs for SQL Remote.

The SQL Remote Message Agent (dbremote) must have access to the current and old transaction logs until they are no longer needed.

A consolidated database no longer needs its transaction logs when all remote databases have received and have confirmed that the messages contained in the transaction logs have been successfully applied.

A remote database no longer needs its transaction logs when the consolidated database has received and confirmed that it has successfully applied the messages contained in the transaction logs.

Backing up remote databases

For your remote databases, you need to decide whether to:

Rely on replication to the consolidated database as a backup method Backup procedures are not as crucial on remote databases as on the consolidated database. You can rely on replication to the consolidated database as a data backup method.
If you choose this method, then you should create a strategy for maintaining the remote database transaction logs. See Maintaining transaction logs for remote databases.
Create a backup strategy for the remote database If the changes made on the remote databases are crucial, then you need to create a backup strategy for the remote databases that includes the maintenance of the transaction logs. See Back up remote databases.

Backing up consolidated databases

The Backup utility (dbbackup) and the SQL Remote Message Agent (dbremote) -x option

On a database, you should never run both the SQL Remote Message Agent (dbremote) with the -x option and the Backup utility (dbbackup).

The -x option is used to manage transaction logs for replication. The -x option ensures that SQL Remote Message Agent has access to old transaction logs and deletes the transaction logs when they are no longer needed. The -x option does not back up the transaction log.

The Backup utility (dbbackup) is used to back up the current transaction log. When the Backup utility (dbbackup) is run with the -r and -n options, it backs up the current transaction log to a backup directory and renames and restarts the current transaction log. The Backup utility (dbbackup) assumes that the current transaction log is the same transaction log that it renamed and restarted after the previous back up.

If you try to run both the SQL Remote Message Agent -x option and the Backup utility (dbbackup) on the same database, they interfere with each other. You can lose transaction logs when both are running.

Only run the SQL Remote Message Agent (dbremote) with the -x option on a remote database that is not being backed up.

Maintaining transaction logs for remote databases
Protecting the consolidated database from media failures
Back up the consolidated database

Discuss this page in DocCommentXchange.