User Tools

Site Tools


public:5292_the_statement_backup_log_is_not_allowed

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
public:5292_the_statement_backup_log_is_not_allowed [2018/01/24 10:25]
edward.chan
public:5292_the_statement_backup_log_is_not_allowed [2018/01/24 10:59] (current)
edward.chan
Line 1: Line 1:
 +===== 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>​
 + ​24/​01/​2018
 + <​br/>​
 + <​br/>​
 + <​b>​
 +  Product Version:
 + </​b>​
 + <​br/>​
 + ​AhsayOBM:​ 7.15.2.0 or above
 + <​br/>​
 + OS: Windows
 +</​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
  
public/5292_the_statement_backup_log_is_not_allowed.txt · Last modified: 2018/01/24 10:59 by edward.chan