debugsqlMinor
Microsoft SQL Server Error 1475 when Mirroring
Viewed 0 times
1475errorsqlmirroringmicrosoftwhenserver
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#164666, answer score: 2
Revisions (0)
No revisions yet.