Unable to mirror a database SQL Server 2012

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

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.

StackExchange Database Administrators Q#115303, answer score: 16