none
Backup clean up task

    Question

  • Hi All,

    We got SQL server database backup clean up task. The database backups are taken to network cif share.

    Thanks

    K


    • Edited by K735 Thursday, April 11, 2013 7:24 AM not required
    Friday, April 05, 2013 3:55 PM

Answers

  • Here it is.

    $dir = "\\10.11.12.00.\ussabcdefbs_bkp\DR_Backup_ABCDPRD1"
    $Destination = "\\10.11.12.00.\ussabcdefbs_bkp\DR_Backup_ABCDPRD1\Saturday "
    
    $date = Get-Date
    
    $Files = get-childitem $dir -include *.bak -recurse 
    
    foreach ($file in $Files)
    {
    if($file.CreationTime.AddDays(30) -gt $date)
    {
    "Do not delete (All the backup will be retained 30 days.) " + $file.FullName 
    }
    elseif ($file.CreationTime.AddMonths(6) -gt $date -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (All Saturday full backups will be retained for 6 Months.) " + $file.FullName 
    Move-Item $file $Destination -Verbose
    }
    elseif ($file.CreationTime.AddYears(7) -gt $date -and $file.CreationTime.Day -lt 8 -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (Monthly first Saturday full backup will be retained for 7 years.) " + $file.FullName 
    Move-Item $file $Destination -Verbose
    }
    else
    {
    Remove-Item $file -Verbose
    }
    }

    It is more efficient do not create Saturday folder as DR_Backup_ABCDPRD1 subfolder on any level.

    Tuesday, April 09, 2013 4:13 PM

All replies

  • are you doing all the backups to one folder on to different folders as per your requirement?? The easier solution would be to backup to subfolders as per your requirement(all full backup to one location, saturday backups to one location,first saturday full backup to one location)and have on maintence plan with mutiple clean up tasks with the required conditions for deletions. Else you will have to use SQL scripts and xp_deletefile to do this, xp_deletefile is not supported to use, but the maintenace plan uses the same behind the scenes.

    refer this for custom script http://www.sqlservercentral.com/scripts/Administration/68440/


    Hope it Helps!!


    • Edited by Stan210 Friday, April 05, 2013 4:23 PM
    Friday, April 05, 2013 4:15 PM
  • I would do it in Powershell and schedule script in SQL server. There is simple script that follows your logic. Remove-Item will delete files.

    $dir = "C:\WINDOWS"
    $date = Get-Date
    
    $Files = get-childitem $dir -include *.bak -recurse 
    
    foreach ($file in $Files)
    {
    if($file.CreationTime.AddDays(30) -gt $date)
    {
    "Do not delete " + $file.FullName 
    }
    else
    {
    if ($file.CreationTime.AddMonths(6) -gt $date -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete " + $file.FullName 
    }
    else
    {
    if ($file.CreationTime.AddYears(7) -gt $date -and $file.CreationTime.Day -lt 8 -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete " + $file.FullName 
    }
    else
    {
    "Delete " +  $file.FullName
    }
    }
    }
    }

    • Proposed as answer by Stan210 Saturday, April 06, 2013 1:51 AM
    Friday, April 05, 2013 9:53 PM
  • Hi K,

    Please execute first script to make sure you get correct result. It does not delete backups but prints “Delete” file name. It will search for .bak files in all subfolders.

    $dir = "\\10.11.12.00.\ussabcdefbs_bkp\DR_Backup_ABCDPRD1"
    $date = Get-Date
    
    $Files = get-childitem $dir -include *.bak -recurse 
    
    foreach ($file in $Files)
    {
    if($file.CreationTime.AddDays(30) -gt $date)
    {
    "Do not delete (All the backup will be retained 30 days.) " + $file.FullName 
    }
    elseif ($file.CreationTime.AddMonths(6) -gt $date -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (All Saturday full backups will be retained for 6 Months.) " + $file.FullName 
    }
    elseif ($file.CreationTime.AddYears(7) -gt $date -and $file.CreationTime.Day -lt 8 -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (Monthly first Saturday full backup will be retained for 7 years.) " + $file.FullName 
    }
    else
    {
    "Delete " +  $file.FullName
    }
    }

    That script actually deletes backup. Please check first prior to execution.

    $dir = "\\10.11.12.00.\ussabcdefbs_bkp\DR_Backup_ABCDPRD1"
    $date = Get-Date
    
    $Files = get-childitem $dir -include *.bak -recurse 
    
    foreach ($file in $Files)
    {
    if($file.CreationTime.AddDays(30) -gt $date)
    {
    "Do not delete (All the backup will be retained 30 days.) " + $file.FullName 
    }
    elseif ($file.CreationTime.AddMonths(6) -gt $date -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (All Saturday full backups will be retained for 6 Months.) " + $file.FullName 
    }
    elseif ($file.CreationTime.AddYears(7) -gt $date -and $file.CreationTime.Day -lt 8 -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (Monthly first Saturday full backup will be retained for 7 years.) " + $file.FullName 
    }
    else
    {
    Remove-Item $file | out-null
    }
    }

    Monday, April 08, 2013 4:14 PM
  • Here it is.

    $dir = "\\10.11.12.00.\ussabcdefbs_bkp\DR_Backup_ABCDPRD1"
    $Destination = "\\10.11.12.00.\ussabcdefbs_bkp\DR_Backup_ABCDPRD1\Saturday "
    
    $date = Get-Date
    
    $Files = get-childitem $dir -include *.bak -recurse 
    
    foreach ($file in $Files)
    {
    if($file.CreationTime.AddDays(30) -gt $date)
    {
    "Do not delete (All the backup will be retained 30 days.) " + $file.FullName 
    }
    elseif ($file.CreationTime.AddMonths(6) -gt $date -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (All Saturday full backups will be retained for 6 Months.) " + $file.FullName 
    Move-Item $file $Destination -Verbose
    }
    elseif ($file.CreationTime.AddYears(7) -gt $date -and $file.CreationTime.Day -lt 8 -and $file.CreationTime.DayOfWeek -eq "Saturday")
    {
    "Do not delete (Monthly first Saturday full backup will be retained for 7 years.) " + $file.FullName 
    Move-Item $file $Destination -Verbose
    }
    else
    {
    Remove-Item $file -Verbose
    }
    }

    It is more efficient do not create Saturday folder as DR_Backup_ABCDPRD1 subfolder on any level.

    Tuesday, April 09, 2013 4:13 PM