1

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?

2
  • 1
    A full backup plus forward log backups can give point-in-time restore. You could do a COPY_ONLY log backup for a second copy if you want. But really, just create a backup of the existing backup if you are really worried. I'd love to know who uses tape backups anymore given the price of HDDs, SSDs and cloud storage such as AWS Glacier Mar 25 at 1:38
  • @Charlieface You mentioned some good points. Thank you!
    – RGO
    Mar 25 at 2:56
6

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.

4
  • 2
    @RGO One thing to note is the MIRROR TO option is only available in Enterprise Edition of SQL Server.
    – J.D.
    Mar 25 at 11:42
  • 1
    @J.D. Thanks, I forgot to mention that
    – HandyD
    Mar 25 at 21:31
  • No prob! Good answer on a good question! I was actually just wondering this myself the other day since I never heard of anyone taking redundant backups of the transaction logs in case of a failure, but it makes sense.
    – J.D.
    Mar 25 at 23:58
  • 1
    @J.D. Thanks for your comment regarding availability of the feature on Enterprise Edition only.
    – RGO
    Mar 26 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.