How do I backup the transaction log for a mirrored database in SQL Server?

Question

I have a mirrored database and am trying to perform a backup of the transaction log, but after it gets to 90% it just hangs and won't go any further.

What is the correct method for backing up a DB that is in the mirroring state?

Solution

Backing up the transaction log on a mirrored database is done exactly the same way as it is done on a non-mirrored database.

The only difference is that records can't be overwritten in the transaction log until the data is mirrored to the partner and until it is backed up.

Check the wait information to see why the process is stalling.

Comments

[Lenin Manoharan]

You are trying to backup the "Principal" right?

[Middletone]

Correct. I'm backing up the principle from the prinicple to an external device. I've tried it both with mirroring active and mirroring paused.

[Lenin Manoharan]

SQL Server 2008 I assume.... So you go to the Management Studio browse to the database which looks like DATABASENAME (Principle, Synchronized) you right click > tasks > backup and you set the "Backup Type" to "Transaction Log" you specify your name and destination and click OK. At this point the backup starts and then pauses at 90%. Is this correct? please verify the state of the database and its synchronization state with the states I describe above Additionally what options are you specifying on the options page? Are you using compression and are you truncating the transaction log?

[Middletone]

It's SQL2005, I'm doing the above steps, right click > tasks > backup | set the "Backup Type" to "Transaction Log" | Device to backup is selected | Under options It's appending the backup and truncating the log. I click OK and then at 90% it hangs (as in it just won't finish and when cancelled it just waits).

[Alex_L]

And full backup still works?

[Middletone]

If you even took 2 seconds to read above you would know that I am trying to back up the PRINCIPAL and not hte mirror. Your link talks aobut backing the mirror which I already know is not going to work and has nothing to do with my question.