3

Could anybody help me please.

I am trying to mirror our SQL Server 2005 to our DR site. I have successfully done half of the databases without any issues.

However, the remaining ones all have the below Microsoft SQL Server Error 1475:

Database mirroring cannot be enabled because the "DBNAME" database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror. (.Net SqlClient Data Provider) I am not sure why this is happening as I am doing nothing different to the ones that work and that is as below. The only difference is these databases are slightly larger in size than the ones now mirroring.

Change recovery Mode to Full, Take a full backup on Principal, Take a log backup on Principal, Copy to DR and restore both with Norecovery, Start Mirroring on the Principal. I have researched the error but most of the comments state to do the above but this is not working for me.

Any help much appreciated.

Improve this question

1 Answer 1

Reset to default
2

On your primary do you have a transaction log backup job running ?

what would be happening is this db is larger by the time restores are completed the primary db would've had another transaction log backup taken, you just need to copy this transaction log backup to the DR and restore and mirroring should initialise.

other option is if you have enough disk space on your log drive you can disable log backups on the primary until you complete mirroring setup and re-enable it.

hope this help.

Improve this answer
3
  • Thank you. Yes that was the issue. I had a log backup job running on specific databases as I mirrored them which didn't include the non mirrored databases but now that job is disabled the databases mirror. Weird I never would of thought that would be the issue but thank you for your advice much appreciated.
    – Richard
    Feb 17, 2017 at 14:05
  • Hi Richard, glad it helped , I hope you have re-enabled the job, essentially until mirroring session is establishes the primary db and secondary db is out of sync, when you ask to mirror, sql checks the consistency of the 2 databases, since mirroring was not established all activity since you took your manual t-log was not able to be mirrored and since t-log backup happened these changes were backed up and taken away from the active log, so mirroring session can't get these changes to apply to your mirror , on a normal mirroring scenario all changes are synced as they happen.
    – entDba
    Feb 18, 2017 at 1:13
  • @Richard please don't forget to mark the answer as accepted and maybe up vote it, we all like some acknowledgement for our efforts. Why not, if it helped your problem. ;)
    – Marcello Miorelli
    Feb 20, 2017 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.