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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply