Sql-server – Unable to mirror a database SQL Server 2012

alter-databasesql servert-sql

When trying to mirror a database using the following command

ALTER AVAILABILITY GROUP SQLAlwaysonGroup ADD DATABASE test0916aj8CJ

I get the following error

Msg 1475, Level 16, State 105, Line 1
Database "test0916aj8CJ" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group.

Can this be done without backing the database? Or should I backup and then discard the backup. It is for a newly created db, so I do not need the backup anyway at this point.

I have tried the following…

BACKUP
DATABASE [test0916aj8CJ] TO DISK = N’NUL’
WITH COPY_ONLY, NOFORMAT, INIT,
NAME = N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD
GO

but the above method did not work either.

Thanks

Best Answer

Its easy to repro the error that you got

Create database in full recovery mode on Primary.
Create database in full recovery mode in Secondary.
Launch GUI and try to configure mirroring between Primary and Secondary.

Below is the error you will get :

Database "test_mirroring_kin" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group. (Microsoft SQL Server, Error: 1475)

Lets understand what that error is :

You configured your database in FULL recovery mode and think that the database is indeed in FULL recovery mode.

The above is not true. After creating the database, if you don't do a FULL backup, even though the database is in FULL recovery mode, it is in pseudo-SIMPLE recovery

You can easily verify it using dbcc dbinfo --> dbi_dbbackupLSN having value of 0:0:0(0x00000000:00000000:0000) or using Paul Randal's script

dbcc traceon (3604)
go
dbcc dbinfo('test_mirroring_kin') with tableresults
go
dbcc traceoff (3604)

Edit: Even taking a first full backup with COPY_ONLY option does not establish a backup chain as well

backup database test_mirroring_kin
to disk = 'D:\test_mirroring_kin_FULL.bak'
with init, stats=10, COPY_ONLY

dbcc dbinfo --> dbi_dbbackupLSN is still having value of 0:0:0(0x00000000:00000000:0000). This means the database is still in pseudo-simple recovery mode.

What you need to do to resolve the above error ?

You need to take a full backup + one transaction log backup on primary and then restore it on secondary database with norecovery and then join the database in AG group or Mirroring.

As a side note and for completeness, for your script telling backup to NUL, read this blog post by Gail Shaw.

Related Solutions

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.

Sql-server – Database mirroring, “WITH NORECOVERY”

Create endpoints on all servers:

CREATE ENDPOINT EndPointName 
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL) 
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, 
                   ENCRYPTION = REQUIRED ALGORITHM RC4)

For a witness server, change ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.

Run the first SET PARTNER command on the mirrored server:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

Notice that it is pointing to the principal server in the command, but you are running it on the mirror.

Then run the next two commands on the principal server, pointing the SET PARTNER at the mirror:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=sync

If you are setting up a witness for automatic failovers, use SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:

ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'

Best Answer

Related Solutions

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

Sql-server – Database mirroring, “WITH NORECOVERY”

Related Question