✎ [V7] ISSUE: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE (MS SQL transaction log backup) [Ahsay Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== [V7] ISSUE: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE (MS SQL transaction log backup) =====

<html>
 <b>
  Article ID:
 </b>
 5292
 <br/>
 <b>
  Reviewed:
 </b>
 2018-01-24
 <br/>
 <br/>
 <b>
  Product Version:
 </b>
 <br/>
 AhsayOBM: 7.15.2.0 to 7.x
 <br/>
 OS: Windows
</html>

<html>
<FONT COLOR=red SIZE=+1>
<B>ATTENTION</B>
1st January, 2022: v7 officially End-of-Life [<A HREF=https://wiki.ahsay.com/doku.php?id=public:announcement:ahsay_v7_eol>details</A>]
</FONT>
</html>

===== Problem Description =====
<html>
When performing a MS SQL transaction log backup (ODBC backup mode), the following warning message is received in the backup report::
 <br>
 <table border="0" cellpadding="0" cellspacing="0" width="850">
  <tr>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    No.
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Type
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Timestamp
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Log
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    ...
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     info
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
   [Start] Backing up "SQL_hostname\MSDB\db_name" using "Transaction log" to "temp_path"
   </td>
  </tr>  
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     <font color=#0040FF>warn</font>
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     <font color=#0040FF>YYYY/MM/DD
     hh:mm:ss</font>
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
   <font color=#0040FF>[Microsoft][ODBC SQL Server Driver][SQL Server]The statement BACKUP LOG is not
   <br>allowed while the recovery model is SIMPLE. Use BACK UP DATABASE or change the 
   <br>recovery model using ALTER DATABASE.</font>
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    ...
   </td>
  </tr>
 </table>
</html>

===== Cause =====
<html>
The message suggests that recovery model of the database in concern is currently set to SIMPLE. When using the simple recovery model, the log is truncated when periodic checkpoints occur. Only full database and differential database backups are allowed.
<br>
<br>
The following table summarizes the recovery models and backup types available with each recovery model:
<br>
<table border="0" cellpadding="0" cellspacing="0" width="850">
  <tr>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Recovery Model
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Full Backup
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Differential Backup
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Transaction Log Backup
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Simple
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Supported
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Supported
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Not Supported
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Bulk-Logged
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Supported
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Supported
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Supported
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Full
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Supported
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    Supported
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Supported
   </td>
  </tr>
 </table>
</html>


===== Resolution =====
<html>
To resolve the issue, modify the recovery model of the database in concern to FULL.

Modify the recovery model of your database by right clicking on the corresponding database in the <b>Source</b> menu of AhsayOBM, and then select <b>Recovery model</b>:
<br>
   <div class="inline-attachment">
    <dl>
     <dt class="attach-image">
      <img src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:SQL_recovery_model.png">
     </dt>
    </dl>
   </div>
<font color=red>Important:</font>
<br>
Perform a Full database backup after you change the recovery model of your database (<a href="https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server">Details</a>):
<br>
<br>
   <div class="inline-attachment">
    <dl>
     <dt class="attach-image">
      <img src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:SQL_full_backup.png">
     </dt>
    </dl>
   </div>
</html>


===== Keywords =====
sql, odbc, log, differential, database, MSSQL, recovery, model
User Tools

Site Tools

Page Tools
