none
Differential Backup fails in SQL Server Failover Cluster Enviornment RRS feed

  • Question

  • Hello,

    I have setup Failover cluster on SQL Server 2014 which working fine. Then I implement backup strategy. It is also working fine.

    But after failover only last differential backup (which is occurred at 12 AM) started failing. From below query I find out that DB snapshot is occurred on 8:30 PM everyday on this node.

    select backup_finish_date,is_snapshot,database_name from msdb.dbo.backupset where is_snapshot = 1

    We don't implement any snapshot for database.

    After failback it is working fine again.Then again I execute above query; And I am surprised now DB snapshot is not happening.

    Can you please explain why the differential backup is failed from only one node and not from second? If snapshot is happening how can I get all information regarding snapshot? (e.g. where is it store? from where is it taken? why snapshot happening from only one node if I have implemented failover clustering? ) 

    Full DB Backup: everyday 2AM

    Differential Backup:  Every 8 hour between 8:00 AM and 7:59:59 AM

    Wednesday, September 7, 2016 10:28 AM

Answers

  • >A differential backup requires a full backup and Master needs to reference the Full backup

    Not quite.  The FULL backup writes the differential base into the database file itself, so a subsequent DIFFERENTIAL backup on another server will work fine.

    eg

    create database backuptest
    
    go
    
    backup database backuptest to disk='nul' with differential
    --Msg 3035, Level 16, State 1, Line 7
    --Cannot perform a differential backup for database "backuptest", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
    
    go
    backup database backuptest to disk='nul' 
    
    go
    backup database backuptest to disk='nul' with differential
    --succeeds
    
    


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, September 7, 2016 3:15 PM

All replies


  • But after failover only last differential backup (which is occurred at 12 AM) started failing. From below query I find out that DB snapshot is occurred on 8:30 PM everyday on this node.

    You should first tell us what is the error you get when diff backup fails. The error would tell us why it is failing. Based on that we can reach to the conclusion. Look into sql server errorlog and post COMPLETE information

    How are you taking backup using TSQL, MP or something else did you made sure the drive where diff backup is putting its files are exactly same for both nodes, ideally it should be.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Wednesday, September 7, 2016 10:40 AM
    Moderator
  • A differential backup requires a full backup and Master needs to reference the Full backup before it will perform a differential backup; (a) have you performed a full backup and (b) is the full backup on a shared resource which all the cluster nodes can access?

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, September 7, 2016 10:54 AM
  • Hello Shanky and Anthony,

    Thanks for reply.

    Backup is taken on shared disk which is mapped with both the nodes. It works on one node and failed for second node only.

    Below is the error 

    Executing the query "BACKUP DATABASE [db] TO  DISK = N..." failed with the following error: "Cannot perform a differential backup for database "db", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
    BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Wednesday, September 7, 2016 11:59 AM
  • Run the following checks from the node that is not working.

    • For the Database in question, right click on it in SSMS, Click Reports\Standard Reports\Backup And Restore Events; review the backup history in the Report and note the path of the last full backup.
    • Try and navigate to the Directory
    • If the Directory exists, script a backup of the master database to the same directory; create a SQL Server Agent Job to perform the backup, ensure the owner is "sa" and schedule the backup for approx. five minutes hence.
    • After you expect the backup to have been performed, check the Job History

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, September 7, 2016 12:14 PM

  • Executing the query "BACKUP DATABASE [db] TO  DISK = N..." failed with the following error: "Cannot perform a differential backup for database "db", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
    BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    This error message says that the full backup does not exists for the differential backup to start. Are you sure you did not missed anything. Are you running any snapshot backup or backup by Third party tool

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 7, 2016 2:09 PM
    Moderator
  • Can you please check if there is any VSS backup/VDI backup happening on the "problem" node. I would suggest you to look at ERRORLOG and look at various backup happening for database.

    Also check KB https://support.microsoft.com/en-us/kb/903643


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, September 7, 2016 2:27 PM
    Moderator
  • you can also see from the default trace using the audit backup(if default trace is enabled) .

    in the sql errorlog it writes all the backups related information's(incase if no trace flag set to skip) so pleas check+you also query msdb tables for backups information(incase if its not purged) so.

    There are some 3rd party tool can perform various backup like redgate,legato etc..


    Regards, S_NO "_"

    Wednesday, September 7, 2016 3:05 PM
  • >A differential backup requires a full backup and Master needs to reference the Full backup

    Not quite.  The FULL backup writes the differential base into the database file itself, so a subsequent DIFFERENTIAL backup on another server will work fine.

    eg

    create database backuptest
    
    go
    
    backup database backuptest to disk='nul' with differential
    --Msg 3035, Level 16, State 1, Line 7
    --Cannot perform a differential backup for database "backuptest", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
    
    go
    backup database backuptest to disk='nul' 
    
    go
    backup database backuptest to disk='nul' with differential
    --succeeds
    
    


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, September 7, 2016 3:15 PM
  • @David agree,

    there are some 3rd party tools can also issues an NUL for the backups, have aware of log backups & might be they also does for DIFF?.



    Regards, S_NO "_"

    Wednesday, September 7, 2016 3:28 PM
  • Backup database db_name to disk='nul' would still be counted as full backup it does not matter whether you dump the content into Nul or some "black whole"

    So YES you NEED a full backup for differential backup.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, September 7, 2016 5:47 PM
    Moderator
  • Hello,

      i have a similar issue when backing up db on a specific cluster's node. It fails on this node and always succeded on the others. On the problematic node, if i manually perform a full backup and then differential, everything go fine.

    I am not able to understand why this happen on that host only. The error msg is exactly the same you showed:

    "BACKUP DATABASE [DbFE] TO  DISK = N'C:\\MSSQL_BACKU..." failed with the following error: "Cannot perform a differential backup for database "DbFE", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
    BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

    I started to think that is a specific config issue. Do you have any tip to follow?

    I am on SQL2016 standard.

    Thanks

    Tuesday, November 12, 2019 9:57 AM
  • You need to check backup history in MSDB database and check if there is a machine backup happened. Generally VSS backup also take backup of SQL databases. Errorlog also would have messaged about backups (unless you suppressed them via trace flag)

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, November 15, 2019 8:33 AM
    Moderator