Misleading Error 1475 While Setting Up Database Mirroring
Wrong Way -->
I was doing some practice work yesterday testing out some mirroring interoperability scenarios when I encountered an error I had not seen before. The error message seemed to indicate that the database I was trying to mirror had some bulk-logged changes that had not been backed up yet. I can easily reproduce this error resulting in the same error message. I’m blogging about it here because the solution isn’t obvious.
Msg 1475, Level 16, State 2, Line 1
Database mirroring cannot be enabled because the “TestMirror” 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.
This seemed like a really odd message. I clearly recalled changing the recovery model of the database from simple to full and then taking a full and log backup that I restored to the mirror server with NORECOVERY. There was some activity on the database after the initial full backup, as I had set up replication between two instances whish included creatin a snapshot which bcp’s out data. The snapshot creation would not qualify as a bulk-logged operation that needed to be backed up.
The problem was that I got my servers mixed up, and was trying to run the initial ALTER DATABASE … SET PARTNER = ‘…’ statement on the replication subscriber rather than the backup I had restored on a third instance. I was trying to enable mirroring on a database that was online and not ready for mirroring. Because it was a brand new database that I had created and it had never been backed up, it gave the warning that it may have bulk logged changes that need to be backed up. I can reproduce the error by simply creating a new, empty database and issuing the ALTER DATABASE … SET PARTNER = ‘…’ statement. If I back up the database, and re-run the statement, I get a more expected error.
So fair warning, if you get this error while trying to set up a new mirroring session, double-check that you’re using the correct databases.
