Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 8 down vote accepted

last_log_backup_lsn will be null in sys.database_recovery_status.

share|improve this answer

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&nbsp 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.