Incremental database backup fails - BACKUP LOG cannot be performed

Posted on 2008-11-04
Last Modified: 2012-05-05

For the past month or so, incremental backups of two specific SQL databases have failed, with the message from the ODBC SQL Driver stating that "BACKUP LOG cannot be performed because there is no current database backup". All other databases on the SQL server in question are successfully backed up incrementally. I Googled around and noticed this is a frequent message when performing backups in SQL server, but I am unsure how to apply this to BrightStor's SQL agent.

What I believe has triggered this issue is that I created a one-time file-based backup of the two databases from within SQL Management Studio, in order to create a snapshot of the databases for our CRM vendor, whom we maintain a tight relationship with. Ever since I manually created that snapshot, incremental backups on just those two databases has failed. Obviously, full backups are fine.

Further, one day I performed another manual file-based full backup, and then a transaction log backup. That night's incremental backup completed. However, other incrementals from that day forward have continued to fail.

I know the message relates to the transaction log backup chain being broken, and there are ways of reconstructing the chain from the last full backup. But I am unsure how this would relate to BrightStor's SQL Agent.

The SQL Server is MS SQL 2005 Enterprise SP1, backed up by BrightStor 11.5 SP3 with the SQL Agent.

What do I have to do in order to get incremental SQL backups for these two databases going again? Thanks!
Question by:DPS_IT
    LVL 13

    Accepted Solution

    You need to initiate a full or differential backup from Britestor (sp?). This will then align the sequence numbers (LSN) in the server; so that transaction logs apply from the most recent full backup.

    In future; when performing adhoc backups; use the COPY_ONLY keyword at the end of the statement; as this it a bit of a ronseal command (Does what is says on the tin)


    Author Comment

    I'll try it. But we run full backups every Friday night, and then incrementals on the weeknights. If the LSNs realign after a full backup, wouldn't it have done so already? Or is it the fact that the full backup is being initiated manually that forces a realign?
    LVL 13

    Expert Comment

    The latter. If you take a full backup inbetween your existing sequence; any transaction log and/or incremential backup will then refer to that inbetween backup.

    If you need to do inbetween backups in future; use the COPY_ONLY function; as this creates a copy only backup that does not disrupt the LSN sequence numbers.
    LVL 4

    Expert Comment

    by:Bill Cochran
    I'm having similar issues.  NTBACKUP adds an entry in the msdb..backupset table when it executes the nightly backup of the file system.  I would like NTBACKUP to stop interacting with SQL Server and only backup the filesystem.  Do you have any idea how to accomplish this?
    LVL 13

    Expert Comment

    Backing up just the *.mdf and *.ldf files is a bad idea as they may not be transactionally consistent unless you shut SQL Server down whilst you perform the backup. Obviously this means your SQL Database is unavailable for the duration taken to complete the backup.

    I would reccomend creating a daily backup job in SQL Server to dump the backup file(s) to disk; then configure NTBackup to collect files from that directory.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
    This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now