1

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?

Middletone
  • 219
  • 1
  • 6
  • 13
  • You are trying to backup the "Principal" right? – Campo Apr 29 '10 at 19:41
  • 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. – Middletone Apr 29 '10 at 19:44
  • 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? – Campo Apr 29 '10 at 20:02
  • 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). – Middletone Apr 29 '10 at 20:07
  • And full backup still works? – Alex_L Aug 16 '11 at 07:40

3 Answers3

1

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.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
0

Please read the simmillar tread.

http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/09210290-cc44-4db8-a8db-8c4c311c40e9

HTH Vinay

Vinay Thakur
  • 96
  • 1
  • 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. – Middletone May 04 '10 at 15:51
0

You should backup your database with COPY_ONLY option (BACKUP DATABASE database_name TO … WITH COPY_ONLY …). It preserves the existing log archive point and, therefore, does not affect the sequencing of mirroring (or log shipping) transaction logs backups.

Check BACKUP command syntax for details

Sergey
  • 2,121
  • 15
  • 14