Resolution for MSSQL Backup Error - "BACKUP LOG Is Not Allowed While The Recovery Model Is SIMPLE" in BDRSuite

Resolution for MSSQL Backup Error - "BACKUP LOG Is Not Allowed While The Recovery Model Is SIMPLE" in BDRSuite

KB ID: 84
Issue:
Encountering the error message: "Msg 4208, Level 16, State 1, Server [servername], Line 1 The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. Msg 3013, Level 16, State 1, Server [servername], Line 1 BACKUP LOG is terminating abnormally" during MSSQL backup.
Cause:
The error occurs when attempting to execute the "BACKUP LOG" command on a database with the recovery model set to "SIMPLE."
Solution:
To avoid the aforementioned issue, follow these recommended backup strategies with MSSQL server plugin in BDRSuite clients:
a) Configure a separate backup job for the 'Master' database with 'FULL' backup schedule alone, as 'DIFFERENTIAL and TRANSACTIONAL' log backups cannot be done on the master database. Also, 'master' has to be restored first when restoring SQL Server for disaster recovery.
b) Databases with 'Recovery Model' as 'Simple' should be configured with 'FULL' backup type and optionally with 'DIFFERENTIAL Log' backup types, as 'TRANSACTIONAL' log backups cannot be performed.
c) Databases with 'Recovery Model' as 'Full' or 'Bulk Logged' should be configured with 'FULL' backup type and optionally with 'DIFFERENTIAL' and 'TRANSACTIONAL LOG' backup.
Additionally, it is recommended to implement the following backup schedule:
1. Execute a full database backup on a monthly basis.
2. Perform differential backups on a weekly basis.
3. Perform transaction log backups on a daily basis.
Adjust the frequency based on the size of your database and acceptable data loss for your company. If databases are changing rapidly, consider a combination of options such as differential backups and transaction log backups to minimize data loss.
For clients with rapidly changing databases, setting a maximum number of kept differential backups helps manage backup space usage. Transaction logs, capturing changes since the last backup, are automatically purged during the next Full/Differential backup schedule.
Implementing a well-thought-out backup schedule aligns with best practices and ensures efficient MSSQL Server backup operations with BDRSuite.

    • Related Articles

    • Resolving MSSQL Backup Error: BACKUP LOG Not Allowed in SIMPLE Recovery Model

      KB ID: 115090 Issue: When configuring MSSQL backups with the Simple recovery model type, the following error message is encountered: Msg 4208, Level 16, State 1, Server servername\databasename, The statement BACKUP LOG is not allowed while the ...
    • Error While Dumping MS SQL Backup Data in the Client

      KB ID: 115082 Error: Dumping MS SQL backup data fails at the BDRSuite client. Scenarios: The above error occurs at the BDRSuite client machine in the following scenarios: Cause 1: MS SQL database(s) configured for backup is set to offline. Solution: ...
    • Error: Multiple Connections to Server or Shared Resource Not Allowed

      KB ID: 115060 Error Message: "Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed. Disconnect all previous connections to the server or shared resource and try again." Issue: The above ...
    • Resolution for Backup Virtually Mounted Error in BDRSuite

      KB ID: 102006 Summary: This knowledge base article provides guidance on resolving the "Backup seems to be virtually mounted in Backup Server. Please unmount and proceed further." error encountered within the BDRSuite applications. The error may arise ...
    • VMware Backup Failed with NBD_ERR_NETWORK_CONNECT Error

      KB ID: 115056 Cause: The error "NBDERRNetwork_Connect" occurs when there is instability in the ESXi/vCenter network during VMware backup/replication/recovery while attempting to read VM’s disk data. Solution: To resolve the "NBD_ERR_NETWORK_CONNECT" ...