Recently, one of our clients requires to backup the AX Database Log files frequently due to the mass transaction files written to database in working hours.
Due to the case study, we decide to backup AX database and log files as below.
1. Implement the full database backup at 6:30pm every weekday
2. Implement the Transaction Log backup every 2 hours (8am – 6pm) in weekdays
3. Implement the Transaction Log files shrink at 6:05pm every weekday
4. Delete Database Backup Files older than 4 days
5. Implement E-Mail notification to Administrator account when the Job completes
As per the requirements above, we configured the SQL Server following the steps in the beneath.
1. create a new Maintenance Plan to backup AX Database daily and schedule it to 8pm in weekdays
2. create a new Maintenance Plan to backup AX Database Log file every 2 hours (8am-6pm) in weekdays
3. create a new Maintenance Plan to shrink Transaction Log files at 6:05pm every weekday
ALTER DATABASE AX2009DatabaseName SET RECOVERY SIMPLE
DBCC SHRINKFILE (AX2009DatabaseLogFilelogicalName, 1)
ALTER DATABASE AX2009DatabaseName SET RECOVERY FULL
The AX2009DatabaseLogFilelogicalName is not the AX database log file’s name. It is the logical name that can be validated via SSMS.
Also, the log files shrink job should be implemented before backup whole database file or another full backup should be implemented between shrink log files and backup log files. Otherwise, all the Log files backup job cannot be implemented.
4. Setup Database Mail and Operator accounts
5. create a new Maintenance Plan to delete AX Old database and log files backup older than 4 days.
6. In SQL Server Agent –> Jobs –> Delete AX database job, right click it and go to Notifications, check email and select the operator that is set up in steps 4 and also choose when to receive the email notification (succeeds? fails? or both)