12

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

Hector
  • 1,042
  • 1
  • 8
  • 22
Bill
  • 273
  • 2
  • 4
  • 7

2 Answers2

16

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)

enter image description here

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)

enter image description here

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.

Kin Shah
  • 62,545
  • 6
  • 124
  • 245
6

Why TO DISK = N’NUL’?

I don't understand why you are using TO DISK = N’NUL’:

BACKUP
DATABASE [test0916aj8CJ] TO DISK = N’NUL’

If you do that, the backup is saved to NUL, (ie. = to nowhere/nothing) and cannot be used because its file does not exist.

While NUL can also be used as a destination for LOG Backups, it should not be used either, especially on Prod servers because LOGs will be lost and the Backup chain will be broken. (~ similar to a SHRINKFILE)

LOG Backup

Before adding a DB to the group, you must prepare it. When you want to prepare a secondary DB, at least 1 Transaction Log backup must be taken and restored. The mirror uses it to figure out which transactions has already been sync on the secondary DB and which transactions are not yet in sync with the primary DB.

Therefore you must Backup the Transaction Logs on the primary DB:

BACKUP LOG [test0916aj8CJ] TO  DISK = N'....bak' 
WITH  COPY_ONLY, FORMAT, INIT,  NAME = N'test0916aj8CJ-Transaction Log  Backup', STATS = 10

The COPY_ONLY option must be used. It makes sure that the Logs are not truncated at the end of the LOG backup.

Primary DB backup chain

However, you cannot restore a log backup alone, meaning without a backup chain (see Kin answer too). This means that the Transaction Log backup must be taken after a FULL Database Backup (+ an optional Differential if needed) has been taken.

Since the COPY_ONLY option does not break the backup chain, it also does not create a backup chain. The COPY_ONLY option cannot be used for the Database Backup.

Backups in order:

  • FULL Database backup without the COPY_ONLY option
  • Optional Differential backup
  • 1 LOG Backup with COPY_ONLY option
  • another (or more) LOG backup if necessary...

Restore the secondary DB

Then the Database Backup must be restored (+ Differential) on the secondary.

It must be restored with the NORECOVERY option because you also want to restore the LOG backup(s) once the FULL Backup has been restored.

Finaly you will restore the LOG Backup. You still need to use the NORECOVERY option because the mirror will keep restoring transactions once in place.

  • Restore the FULL backup with the NORECOVERY option
  • Restore the DIFF backup with the NORECOVERY option
  • Restore all LOG backups in order with the NORECOVERY option

Lets put it all together (adapt it to your env)

  • On the Primary server run:

    USE master
Go
BACKUP DATABASE [test0916aj8CJ] TO DISK = N'....bak'
WITH FORMAT, INIT, NAME = N'test0916aj8CJ-Full Database Backup', STATS = 10
GO
BACKUP LOG [test0916aj8CJ] TO DISK = N'....bak' 
WITH COPY_ONLY, FORMAT, INIT, NAME = N'test0916aj8CJ-Transaction Log Backup', STATS = 10
GO

  • On the Secondary server run:

    USE master
Go
RESTORE DATABASE [test0916aj8CJ] FROM DISK = N'....bak' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [test0916aj8CJ] FROM DISK = N'....bak' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

  • You can then proceed with adding the new secondary DB to the availibility group...

Optional actions

  • It is better to set the DISK option to a shared folder that is available from both the Primary and Secondary servers.
  • It is also better to store DB files on similar disk and location on both the Primary and Secondary servers.
Julien Vavasseur
  • 10,180
  • 2
  • 28
  • 47