:::: MENU ::::

Log backups fail after changing the SIMPLE recovery model

  • Dec 13 / 2012
  • 0
dbDigger, Disaster Recovery, Maintenance plans, SQL Server Agent scheduled Jobs, SQL Server Error messages, Transaction log files

Log backups fail after changing the SIMPLE recovery model

Recently a scenario was shared with me where maintenance plan was failed to create the log backups. Actually recovery model of DB was set to SIMPLE to prevent the log file population for some log intensive bulk operations. After completing the operations recovery model was put back to FULL. Every thing was OK till this point but maintenance plan job began to failed later for creating log backups of that database with following error

BACKUP LOG cannot be performed because there is no current database backup.

The reason for error is that after changing the recovery model of DB from SIMPLE to BULK LOGGED or FULL, we have to create a full or differential backup before going for log backup.
Solution to avoid such error is simple that we have to go in following sequence

  • Change recovery model to simple
  • Complete the required operations
  • Change back to FULL or BULK LOGGED recovery model
  • Create FULL or DIFFERENTIAL backup
  • Successfully create log backups
  • Here is a quick demo to simulate the above steps

    USE [master]  
     GO  
     -- Set the DB to simple recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY SIMPLE  
     GO   
     -- Set the DB to full recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY FULL  
     GO   
     -- Try to Backup the log, it would fail with error  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
     -- Now Create full or differential backup of database  
     Backup DATABASE DBDIgger   
     to Disk = 'C:DBD-FullBackup.bak'   
     GO  
     -- Try to Backup the log, it would be OK  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
    
    Consult us to explore the Databases. Contact us