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?
5
-
You are trying to backup the "Principal" right?– CampoApr 29, 2010 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.– MiddletoneApr 29, 2010 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?– CampoApr 29, 2010 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).– MiddletoneApr 29, 2010 at 20:07
-
And full backup still works?– Alex_LAug 16, 2011 at 7:40
Add a comment
|
3 Answers
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.
Please read the simmillar tread.
HTH Vinay
-
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. May 4, 2010 at 15:51
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
