When you have a database which has just been created and is in the FULL recovery model, or you have an existing database you are changing from SIMPLE to FULL, you need to take a full backup before you are able to take log backups, as there needs to be a starting point for the log chain. There is a little-known issue which can catch you out that usually happens in environments where differential backups are also being used, and that is the over-use of COPY_ONLY as a backup option.
If your automated backups include differential backups, the tendency is to always use COPY_ONLY for ad hoc full backups, as this ensures that when taking manual backups you do not accidentally change the file which the next differential would be linked to. The general understanding is that all COPY_ONLY does differently from a normal full backup is prevent the backup process from resetting the changed flags on the data pages, and that is the limit of its functionality.
It does do that, but there are other differences which can cause issues.
It tries to be as zero-touch as possible, and as well as not resetting the page flags, it also does not initialise the log chain. So, in the above scenario if you have only taken a COPY_ONLY full backup, and then try to take a log backup, it will fail. Which is not necessarily intuitive as COPY_ONLY is understood to only relate to differential backups.
The short answer to this problem is that COPY_ONLY full backups cannot be used to start a log backup sequence, it must be a normal full backup. Only a normal full backup will ensure that the meta-data is set correctly to work properly in FULL recovery mode.
This is straightforward to show, so let’s see this in action:
Create a test database:
CREATE DATABASE Test;
Ensure the database is in FULL recovery (if not already):
IF ((SELECT recovery_model_desc FROM sys.databases WHERE name = 'Test') <> 'FULL')
BEGIN
ALTER DATABASE Test SET RECOVERY FULL;
END
We take our full COPY_ONLY backup:
BACKUP DATABASE Test
TO DISK = 'C:\BACKUP\Test.copyonly.BAK'
WITH COPY_ONLY;
/*
BACKUP DATABASE successfully processed 337 pages in 0.053 seconds (49.648 MB/sec).
*/
And now try to take a transaction log backup:
BACKUP LOG Test
TO DISK = 'C:\BACKUP\Test.log.BAK';
/*
BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.
*/
Why did this fail, as we have just taken a full backup? To see what is going on requires looking at the meta-data in the database. To take a transaction log backup requires that a log backup chain has been established, and you can see this in two places: in the header pages in the database, and more easily in the system view sys.database_recovery_status.
If you query sys.database_recovery_status and there is a NULL for the last LSN, that means the log chain has not been initialised:
SELECT last_log_backup_LSN
FROM sys.database_recovery_status
WHERE database_id = DB_ID('Test');
-- NULL
What this means is that even though a full backup has been taken, because it was COPY_ONLY the database is left in what is known as autotruncate mode, as it has not been properly initialised. It looks like it is in FULL recovery mode, but will behave as though it is in SIMPLE, and will clear down completed log records automatically and prevent log backups from being taken. This is not intuitive, as the documentation only describes how the purpose behind COPY_ONLY is to not affect differential backups.
If we now run a normal full backup:
BACKUP DATABASE Test
TO DISK = 'C:\BACKUP\Test.full.BAK'
WITH INIT;
/*
BACKUP DATABASE successfully processed 338 pages in 0.042 seconds (62.720 MB/sec).
*/
And check the meta-data again:
SELECT last_log_backup_LSN
FROM sys.database_recovery_status
WHERE database_id = DB_ID('Test');
-- 33000000007500037
We can now see that an LSN has been created, which will allow log backups to run:
BACKUP LOG Test
TO DISK = 'C:\BACKUP\Test.log.BAK';
/*
BACKUP LOG successfully processed 2 pages in 0.005 seconds (2.636 MB/sec).
*/
So what does this mean practically? In an environment where differential backups are being used, it still makes sense when taking manual backups to use the COPY_ONLY option, as this will not accidentally affect anything. But, for the crucial first backup after a database is created or after it changes recovery model, the COPY_ONLY option cannot be used as it will not be initialised as a FULL recovery database, and it will block log backups until it is.