Made the change discussed here.
Again, the change was to temporary alter our Recovery Mode to SIMPLE and back. In between, shrunk the transaction log files.
Ran the Ola Hallengren’s transaction Log backup step, but nothing is being backed up.
BTW, the Scripts are available here.
Let us review the scripts and see where we are failing.
Stored Procedure – dbo.DatabaseBackup
sys.database_recovery_status – Get last_log_backup_lsn
Check sys.database_recovery_status and get last_log_backup_lsn
IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' BEGIN SELECT @CurrentLogLSN = last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID END
- Skip when LogBackup
- Recovery Is Simple
- Or CurrentLogLSN is null
SELECT tblSDRS.[database_id] , [databaseName] = db_name(tblSDRS.[database_id]) , tblSDRS.last_log_backup_lsn FROM sys.database_recovery_status tblSDRS
Database Backup – Directly
If we try to backup the database directly by issuing our own “backup log <database-name>” statement. i.e.
BACKUP LOG [eiadev] TO DISK = N'Z:\Backups\eiadev_20161027_1208PM.bak' WITH NOFORMAT, NOINIT , NAME = N'enr_Log Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Msg 4214, Level 16, State 1, Line 7 BACKUP LOG cannot be performed because there is no current database backup. Msg 3013, Level 16, State 1, Line 7 BACKUP LOG is terminating abnormally.
Take full backups right away or wait till our next scheduled full backup.
Once that occurs, our transaction backups will run to successful completion.
So basically because we changed the Recovery Mode from FULL to SIMPLE ( and now back to FULL), we can not issue a transaction log backup statement until we take a FULL Backup.
To avoid that error, Olla’s script proactively checks the sys.database_recovery_status dmv.