none
need to automate the cleanup of backups from disk periodically

    질문

  • need to automate the cleanup of backups from disk periodically Not sure if there is some way to do it via SQL Server maintenance plans or do we have to write a cmd script to clear those.

    Please help me.

    2012년 6월 21일 목요일 오전 2:12

답변

  • Add a cleanup for each maintenance task.

    When the backup completes successfully, then the old backup can be deleted by the cleanup task.

    • 답변으로 제안됨 vr.babu 2012년 6월 25일 월요일 오전 5:50
    • 답변으로 표시됨 Mayur-DEW 2012년 6월 26일 화요일 오전 9:49
    2012년 6월 23일 토요일 오후 10:18

모든 응답

  • There is option for this in Maintenance Plans. You will need to add task named "Maintenance Cleanup Task" into maintenance plan (or create a separate maintenance plan for cleanups) and specify the search criteria and file age. I hope this will solve your purpose.

    Kindly mark the reply as answer if they help

    2012년 6월 21일 목요일 오전 5:43
    1. Click the "Maintenance Plan" button and select "Maintenance Wizard" to start the cleanup scheduler. Click "Next" on the first welcome window.
    2. Type a name in the "Name" text box. This is any friendly name that you recognize for your cleanup task. Click "Next." Select "Maintenance Cleanup Task" as the schedule task type and click "Next."
    3. Select "Backup files" in the "Delete files of the following type" section. Enter the location for your SQL Server backup directory and then enter "bak" in the "File Extension" text box. Click "Next."
    4. Check the box for "File Age" and enter the age of the backup files you want to delete. For instance, if you want to keep 2 days of backups and delete all older backup files, enter "2 Days" in the section labeled "Delete files older than the following." Click "Next."
    5. Review the changes and settings you've made and click "Finish" to delete the backup files on your hard drive.

    • 편집됨 irusul 2012년 6월 21일 목요일 오후 2:15
    • 답변으로 제안됨 vr.babu 2012년 6월 25일 월요일 오전 5:50
    2012년 6월 21일 목요일 오후 2:15
  • Hey friend,

    I have three different maintenance plan weekly_db_1, weekly_db_2 & weekly_db_3 all these plans included different databases.

    & need to delete older backup files (retention period ex: 28 days) Suppose i will use maintenance clean up task. then my doubts are

    1. do i need to set Maintenance cleanup task for each of my backup maintenance plan?

    2. In clean up task the option is provided a bak as a file name. then suppose my backup plan fails, so will it be deleted my older files?

    3. because i need is, my  cleanup task will run only when my backup completed successfully.

    Guys pls help me as i am new DBA.

    2012년 6월 22일 금요일 오전 7:27
  • Hi,

    To avoid confusion:

    1. Set each weekly maintenance plan to use a different backup folder e.g. x:\sqlbackups\weekly_db_1,x:\sqlbackups\weekly_db_2,x:\sqlbackups\weekly_db_3.

    2. Set a success constraint between the backup and backup cleanup task in each maintenance plan.

    This should keep each maintenance plans backups separate and the associated backups of the plan are not removed unless the maintenance plan backups are successful.

     

    • 답변으로 표시됨 Mayur-DEW 2012년 6월 25일 월요일 오전 4:33
    • 답변으로 표시 취소됨 Mayur-DEW 2012년 6월 26일 화요일 오전 4:38
    2012년 6월 23일 토요일 오전 2:23
  • Add a cleanup for each maintenance task.

    When the backup completes successfully, then the old backup can be deleted by the cleanup task.

    • 답변으로 제안됨 vr.babu 2012년 6월 25일 월요일 오전 5:50
    • 답변으로 표시됨 Mayur-DEW 2012년 6월 26일 화요일 오전 9:49
    2012년 6월 23일 토요일 오후 10:18
  • So, what is the option if i want to use the " backup set will expire" option instead of creating separate MP plan for cleanup task. If this option will not delete old files, then why sql has provided it.

    Pls help me

    2012년 6월 26일 화요일 오전 4:39
  • The "backup set will expire" is a safety net that helps you prevent yourself over writing your backups.

    2012년 6월 26일 화요일 오전 4:46
  • Hi friends,

    I have used this option "backup set will expire"  & checked but still my old backup files are exist in folder. i.e it doesn't work.

    2012년 6월 26일 화요일 오전 4:52
  • The purpose of "backup set will expire" is not to automatically delete old backup files, but rather to specify when the backup set can be overwritten by another backup set. In order to delete old backup files, see irusul's response.

    Steve

    2012년 7월 11일 수요일 오후 8:58