Take the 2-minute tour ×
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. It's 100% free, no registration required.

SQL Server 2005.

I have DB with full recovery model. If i execute on my base

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

or

BACKUP LOG test WITH  truncate_only

then if I try run log backup

BACKUP LOG test TO  DISK = 'backupfile.bak'

I receive error message

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

Question:
How do I check that I was not able to make a log backup. Without running backup log.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

last_log_backup_lsn will be null in sys.database_recovery_status.

share|improve this answer
    
Right. Thank you very much. That's what I need. –  Arty Jun 22 '13 at 19:22

You need to do a FULL backup of your database before you do a LOG backup.

execute below T-SQL to see if the logbackup exists or not :

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 and the value of last_log_backup_lsn = NULL means that the database is not maintaining a sequence of log backups and it is in auto-truncate mode.

refer this KB article.

below is the 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
share|improve this answer
    
Thanks, but Remus Rusanu was faster. –  Arty Jun 22 '13 at 20:00
1  
@Arty Does not matter to me .. As long as we help the community and each other .. Glad that you found your answer ! –  Kin Jun 22 '13 at 20:27
    
+1 This answer is better –  Paul White Jun 25 '13 at 14:08

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.