July 10, 2012 at 7:42 am
Hi, I currently backup my databases everynight and also run a maintenance plan that deletes backup files that are more than 1 month old. Is there an option in Management Studio that will allow me to keep one backup copy of each database file from each month that is more than 1 month old (ie. Today is 7/10/12, I would like my maintenance plan to delete all but one set of backups for the period 5/10/12 to 6/10/12)? Or is this something that will have to be done in Visual Basic?
July 10, 2012 at 8:21 am
possibly powershell would work for you. You can run a powershell step from sql agent.
Something like this should delete all files older than 30 days except except file that was modified on the first of the previous month. note: I have not tested the exclusion of the file on the first of previous month as i did not have one readily available.
$Now = Get-Date
$Days = “30”
$TargetFolder = “c:\temp”
$LastWrite = $Now.AddDays(-$days)
$PreviousMonth = $Now.AddMonths(-1)
$firstDayOfMonth = Get-Date ((((Get-Date $PreviousMonth).Month).ToString() + "/1/" + ((Get-Date $PreviousMonth).Year).ToString() + " 00:00:00"))
get-childitem $TargetFolder *.dmp | Where {$_.LastWriteTime -le “$LastWrite”} |where {$_.LastWriteTime -ne “$$firstDayOfMonth”} |Remove-Item
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply