THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Database mirroring requires transaction log backup regardless

I was setting up mirroring at a client site today and started as I usually do by backing up the primary database and restoring it at the mirror. It would not start mirroring and complained that I didn't have the latest transaction log backup: the mirror database …., has insufficient transaction log data to preserve the log backup chain of the principal database.

Somewhere along the way, the need to have a transaction log backup has crept in. So, to start mirroring, I just now backup the primary database to a file, backup the transaction log to the same file and then restore both on the mirror server. Then it starts fine.

This seems pointless behaviour and it didn't used to work that way. I have an RTM SQL Server 2005 in a virtual machine and it works fine without doing this. Anyway, thought that posting it might help someone.

Published Monday, August 18, 2008 8:07 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

alphatross said:

I first started using DB Mirroring in SQL 2005 SP1, and even back then you had to back up \ restore the Transaction Log in addition to the Database Backup, so it must have crept in sometime between 2005 RTM and SP1. It can be annoying if a Sched. Job \ Maint. Plan for a TLog Backup occurs in the middle! Also, there's the Bug where final ALTER DATABASE ... SET PARTNER = ... statement times out after 10 secs for a large Transaction Log file (not Log contents, just *.LDF actual file size - it can be 99% free space!) that wasn't fixed until 2005 SP2 CU 6 or 7, so some read through must take place in the actual live Transaction Log during Mirroring start, which makes you wonder why we need to backup\restore the Log on Mirror.

August 18, 2008 7:21 AM
 

Ali said:

yes ,thats true ,it also happened to me a couple of days ago ,it looks like DB mirroring requires that the LOG should have  some information,when you only restore a FULL backup ,the log will be empty.

August 18, 2008 9:35 AM
 

TiborKaraszi said:

Hi Greg,

I've actually noticed this on 2005 sp2. For instance, I usually demo db mirroring in class and at one point in time I had to adjust my scripts to add a log backup. I don't have my demo files in source control, if I did I could have tell you when I had to implement this change. Perhaps it was with sp2? Or perhaps there was some other change in my script that forced this, and it isn't version dependent? I don't know. But I do know for sure that I had to do this on 2005 as well...

August 18, 2008 12:26 PM
 

Darren Neimke said:

Hi Greg, is it right to assume that you are referring to SQL Server 2008 behaviour here?

August 18, 2008 4:04 PM
 

Greg Low said:

Hi Darren,

No, it's 2005 that I was referring to. I'm sure it'll be the same for 2008.

Regards,

Greg

August 18, 2008 4:49 PM
 

sql_noob said:

does anyone do db mirroring for 100GB and larger DB's with a DR site in another state?

we used to do log shipping in the 2000 days and stopped because of constant problems where we had to restore a full backup and restart it from scratch. now we do SRDF from via our EMC

i wasn't here at the time, but that is the story i hear

August 20, 2008 3:45 PM
 

Greg Low said:

Yes, but in each case, there's usually some clever partitioning of data into read-only and read-write filegroups. That way you rarely need to copy huge amounts over.

August 21, 2008 11:20 PM
 

Larry said:

Well I just mirrored 6 databases from the primary server to the mirrored server.  It was not until I got to the 3rd database did I receive this error requiring a transaction backup.  Even funnier is the whole database was 7 MB backed up and the trn was like 200KB.  The first two databases were 50 and 90 MB and did not throw an error.  Very strange.

September 17, 2009 4:47 PM
 

Rob Volk said:

I know this is an old thread, but I found another wrinkle with the log backup requirement: you may not be able to use a COPY_ONLY log backup to initialize mirroring.  Just had 3 of 5 databases generate the same error message with COPY_ONLY backups, and then succeeded without it.

March 11, 2013 4:27 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement