SQL Server: Should the same log records be backed up multiple times?

Question

I came to SQL Server database administration from an Oracle DBA background. So maybe naturally, I always try to understand the differences between the two DBMSs as well as the similarities. One question that have struck me recently is that, should the log records be backed up multiple times, and how? In Oracle database, assuming the database is in the "Archive-Log" database mode, the "redo" log files are archived as separate "archived redo log" files, whose role will be critical in case of a point-in-time recovery scenario, and so because of that, it's very important for these files to be backed up regularly. Often times, DBAs take multiple backups of each "archived redo log" file so that if, for example, a particular tape is lost or damaged, there would still be another copy of the same backup available somewhere else on some other backup device. This will obviously provide a high level of protection against a data loss.

In SQL Server, as we know, there are transaction logs containing log records, which play avital role for performing database recovery. Now my question is this: for a database that is on the "FULL" recover model, would it be possible to backup the log records multiple times? And if possible, would it be a good idea? And how that can be done?

Answer 1

The archive redo log is roughly equivalent to a transaction log backup in SQL Server, and there are options within SQL Server to take multiple copies of the transaction log backup simultaneously.

When performing your BACKUP LOG operation, you can specify MIRROR TO to take multiple copies of the same backup at different locations, providing protection against disk/tape failure.

There is also some useful information here.