Hello,
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:
USE ReportServer;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ReportServer
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ReportServer_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ReportServer
SET RECOVERY FULL;
GO
And the CMD file which run as post command:
osql -i shrink.sql -U sa -P ******** -S 192.168.0.75 -d ReportServer
Unfortunately this SQL script not gave is successful result: we got always this annoying exception message.
Out question is: how we should backup SQL to dismiss this exception?
Thanks the answers, regards,
Zsolt
