SQL Server Error 4208: BACKUP LOG Not Allowed in SIMPLE Recovery Mode

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 recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Solution:
To resolve this error and successfully configure backups for MSSQL databases with the Simple recovery model, please follow these recommended steps:
1. Utilize backup strategies with the MSSQL server plugin in BDRSuite Backup Server and BDRSuite Clients.
2. Note that 'TRANSACTIONAL' log backups are not supported for databases using the Simple recovery model. To address this issue:
a) Edit the backup schedule and uncheck/remove the T-Log backup schedules for the affected database.
b) Alternatively, create a new Backup Job specifically for this database, excluding 'TRANSACTIONAL' log backups.
3. Implement the following backup strategies:
a) For the 'Master' database, configure a separate backup job with a 'FULL' backup schedule only. 'DIFFERENTIAL' and 'TRANSACTIONAL' log backups cannot be performed on the master database. Additionally, restoring the 'master' database is necessary as the first step in SQL Server disaster recovery.
b) Databases using the 'Recovery Model' as 'Simple' should be set up with 'FULL' backup types and optionally 'DIFFERENTIAL Log' backup types, as 'TRANSACTIONAL' log backups are not supported.
c) For databases using 'Recovery Model' as 'Full' or 'Bulk Logged,' configure 'FULL' backup types and optionally include 'DIFFERENTIAL' and 'TRANSACTIONAL LOG' backups.
For more information and detailed instructions, please refer to the following link : Microsoft SQL Server Documentation

    • Related Articles

    • 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 ...

    • 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 ...

    • MSSQL Backup Failure: SQL Writer Failed

      KB ID: 113 Problem: When attempting to perform an MSSQL backup, users may encounter a failure with the error message "SQL Writer Failed." Cause: The "SQL Writer Failed" error can occur to various reasons, including: 1. VSS (Volume Shadow Copy ...

    • Circular Logging and Log Backup Issue

      KB ID: 90 Cause: Error while preparing Storage Group [First Storage Group] for backup: "An Incremental/Differential log backup cannot be performed when circular logging is enabled.” This error typically occurs when the circular logging option is ...