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.
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 ...
VMware Backup and Recovery
Overview BDRSuite offers agentless backup solutions for VMware ESXi & vCenter. This eliminates the need to install backup agents on each VM. It also provides flexible storage, instant & granular recovery, and centralized management for efficient VM ...