5

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?

1

2 Answers 2

9

last_log_backup_lsn will be null in sys.database_recovery_status.

0
9

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
4
  • Looks documented to me learn.microsoft.com/en-us/sql/relational-databases/… Sep 1, 2022 at 11:40
  • Also why WHERE [database_id] = DB_ID (@DBName) why not just WHERE name = @DBName. And you could do this whol code much shorter if you join the two views SELECT CASE WHEN recovery_model = 1 AND last_log_backup_lsn IS NOT NULL THEN 1 ELSE 0 END IsReallyFull FROM sys.databases d JOIN sys.database_recovery_status dr ON dr.database_id = d.database_id WHERE d.name = @DBName Sep 1, 2022 at 11:47
  • @Charlieface this was written in 2013 and at that time sql server did not have this documented. Yes the tsql can be improved .. appreciate your comment, but please be mindful that you are commenting on an answer written in 2013 and things have changed since then.
    – Kin Shah
    Sep 3, 2022 at 23:45
  • Yup sorry didn't notice, think it randomly just came up as Hot Network Post Sep 4, 2022 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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