Backup Maintenance Plan - Best Practices RRS feed

  • Question

  • Hi All,

    I'm about to embark on creating a maintenance plan to back up all databases on one of our SQL 2005 servers. I am looking for some advice on best practices for doing this.

    I have it in my mind that i want to be taking a full database backup once a week, with differential backups on a daily basis and transactional backups performed every 2 to 4 hours.

    Do i need to create three maintenance plans for this, i.e. 1 for full, 1 for differential, and 1 for transactional?

    If i want to only keep the backups from the last week, is this done by setting up a maintenance cleanup task in the full backup plan to clear all bak files that are a week old?
    If so i'll also probably require one to remove the trn files also.

    When using the backup command from the context menu in SSMS there is an option to name the backup set. How does this work when using maintenance plans as i haven't been able to find this option whilst trying out some of the features?

    I'm sure to have more questions on this subject, but any help on the above queries would be most appreciated.


    Wednesday, April 11, 2007 11:40 AM


  • No.  As long as you are running SP2, you can do all of this using subtasks.  So, you can create a single maintenance plan and then add subtasks for the full, differential, and tran log.  Each of the subtasks can have their own schedule.
    Thursday, April 12, 2007 9:20 PM