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.
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.
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.
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…
Hi Greg, is it right to assume that you are referring to SQL Server 2008 behaviour here?
Hi Darren,
No, it's 2005 that I was referring to. I'm sure it'll be the same for 2008.
Regards,
Greg
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
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.
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.
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.