BACKUP LOG cannot be performed because there is no current database backup

I have a SQL Server 2005 database using the full recovery model. If I execute the following commands:

ALTER DATABASE test SET RECOVERY SIMPLE;
ALTER DATABASE test SET RECOVERY FULL;

or

BACKUP LOG test WITH  truncate_only;

Then try to run a log backup:

BACKUP LOG test TO  DISK = 'backupfile.bak'

I receive the error message:


BACKUP LOG cannot be performed because there is no current database backup.

Question:

How do I check that I will not be able to take a log backup, without running a backup log command?

You need to do a full backup of your database before you can take a log backup.

Execute the below T-SQL to see if a log backup can be performed:

SELECT db_name(database_id) as 'database', last_log_backup_lsn 
FROM sys.database_recovery_status

Note : sys.database_recovery_status is an undocumented system view. If the value of last_log_backup_lsn is NULL, it means the database is not maintaining a sequence of log backups and is in auto-truncate mode. Refer to the following Microsoft Knowledge Base article:

You must perform a full database backup before you back up the transaction log for a database

Below is a script from SQLSkills to check if your database is in "pseudo-simple" mode.

USE [msdb];
GO

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'SQLskillsIsReallyInFullRecovery')
    DROP FUNCTION [SQLskillsIsReallyInFullRecovery];
GO

CREATE FUNCTION [SQLskillsIsReallyInFullRecovery] (
    @DBName sysname)
RETURNS BIT
AS
BEGIN
    DECLARE @IsReallyFull  BIT;
    DECLARE @LastLogBackupLSN  NUMERIC (25,0);
    DECLARE @RecoveryModel  TINYINT;

    SELECT
        @LastLogBackupLSN = [last_log_backup_lsn]
    FROM
        sys.database_recovery_status
    WHERE
        [database_id] = DB_ID (@DBName);

    SELECT
        @RecoveryModel = [recovery_model]
    FROM
        sys.databases
    WHERE
        [database_id] = DB_ID (@DBName);

    IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)
        SELECT @IsReallyFull = 1
    ELSE
        SELECT @IsReallyFull = 0;

    RETURN (@IsReallyFull);
END;
GO

StackExchange Database Administrators Q#45095, answer score: 9