SQL Server Database backup for AX

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

USE AX2009DatabaseName
GO
ALTER DATABASE AX2009DatabaseName  SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AX2009DatabaseLogFilelogicalName, 1)
GO
ALTER DATABASE AX2009DatabaseName  SET RECOVERY FULL
GO

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)

Advertisements
This entry was posted in Dynamics AX and SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s