Backing up SQL Databases | Full / Simple Recovery Mode Issue
We are using BE 2012 SQL Agent to backing up our critical SQL DB.
Our backup strategy: Weekly full backup + daily differential backup.
Our problem is, that our business needs to use SQL full recovery mode to keep ability restoring SQL DB with limited or no transaction loosing.
After 10 successful full backup, we got an exceptions as indicated in the following job completion message; "V-79-40960-37914 - Database database_name is configured to maintain transaction logs. Transaction log backups are not being performed. This will result in the log growing to fill all available disk space. Regular log backups should be scheduled or the database should be changed to the simple recovery mode."
We found the explanation, that BE 2012 should maintain the log backup. However in BE the SQL log backup is possible just with incremental backup, not with differential or Full backup.
What should be do? We don't wanna change Full+Diff backup strategy and we need Full SGL DB recovery model.
We figured out, we will create a simple SQL script and we will run after every Full backup as CMD with post command.
Here what we are doing: