web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Scheduling SQL Backups...
Microsoft Dynamics AX (Archived)

Scheduling SQL Backups for AX

(0) ShareShare
ReportReport
Posted on by In AX2012 in the In... 499

Hi

Need help on Scheduling SQL Backup through SQL Management Maintenance Plans.

SQL & AX Information. 

1.Version:SQL Server 2008 R2.   2.Database Properties Options-->Recovery Model: Simple--> 3.Files a.Mdf file Autogrowth By 200 MB, unrestricted growth b.Ldf file By 10 percent, unrestricted growth .

AX Version :AX2012 R3 with CU8

SQL Management Plan Information.

1.Backup Type:Full.  2.Set Backup compression :Compress backup 3.Backup File extension is bak.

My Observations

Earlier in Set Backup compression  it was not Compression backup, in this case my Backup file size was around 30 GB.

Later when Set Backup compression :Compress backup my backup size was around 3 GB.

My Requirement 

1.For every 3 hours i need Differential Backup and 2.For every 1 hour i need Transaction log.

My Questions related to Restoring these databases

1. This compressed backup if it is restored , what will be Performance w.r.t AX whether Performance increases or decreases.

or it is suggested to have the backup without this compression 

2.For Example on Feb 1st at 8PM  i have full backup , on 2nd Feb if something goes wrong at 4pm  and i have to restore the database.

My Differential backups are available for 2nd Feb (9am.12pm,3pm) My Transaction log are available for (9am,10am,11am,12pm,1pm,2pm,3pm).What should be the sequence of Restoring

3.What should be the case of selection with recovery and with no recovery.

4.Database-->Tasks-->Shrink-->Database is it suggested to do on regular basis?

Please help me with other considerations if i had missed out which may be dangerous or anything which may be good for performance.

Thanks in advance

Regards

Raghu

 

 

 

 

*This post is locked for comments

I have the same question (0)
  • Mariano Gracia Profile Picture
    Mariano Gracia on at

    If your recovery model is simple you don't need to backup your transaction log, because each time a transaction is commited, the data is transferred from the log file to the data file. From the msdn "The simple recovery model provides the simplest form of backup and restore. This recovery model supports both database backups and file backups, but does not support log backups"

    I recommend you to take a look to SQL forums

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    First of all your database setup is wrong. You need Full recovery model for a Production environment, otherwise in case of an outage you may lose a lot of data. Percentage-based file growth is also a bad practice, it is typically set between 100 to 500 MB growth for log and data for AX databases. I would strongly recommend you set up the System diagnostic tool from the Microsoft LifeCycle Services website to reveal the underlying problems with your configuration: ax.help.dynamics.com/.../system-diagnostics-lifecycle-services-lcs

    A typical backup regime does not match your schedule. I am operating with a 2 TB AX database size, and we take weekly Full database backups, daily Differential database backups, and every 15 minutes we take the Transaction log backup. Transaction logs can be taken as frequently as your business is comfortable with losing how much data when rolling back.

    With this in case we need to fall back, we need to restore:

    - 1x last week's full backup

    - 1x last night's differential backup

    - all transaction logs taken since the differential.

    Differential backup is just an accumulation of all transactions in the database which have happened since the last full backup's timestamp, so having multiple of those is not useful, the latest one contains all the previous one's information. The same is true for transaction logs taken before your last differential, they are outdated.

    Whether you store a backup compressed or not does not matter, because the restored data will be identical to the original one, if your backup file is not corrupt (you took backup with Checksum and/or Verify, or you do a Verify on a separate SQL server).

    When you restore, doing a norecovery means you intend to restore more files. That means all files up to the latest transaction log you want to restore you do not do recovery, and with the latest restore you do the recovery to bring the database online.

    You should NEVER shrink a database, since that will cause you enormous index fragmentation and will pretty much kill your performance.

    Finally, you should consider buying a Safari Books Online subscription and read the dozens of SQL Server books, they are excellent.

  • Verified answer
    In AX2012 in the InventOnHandItemListPage I need to be able to f Profile Picture
    In AX2012 in the In... 499 on at

    Vilmos and Mariano

    Thank you very much for your help. As for as now you have clearly addressed my queries one by one .

    I may need your help in further , i will get back to you again .

    Thanks once again

  • Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    Please make sure you are not marking your own answer as Verified, but the answers which were helpful on the right hand side of the post, to assist other users with similar problems.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans