Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

When I delete a full backup, I would like to delete all the files that are rendered useless once that full backup has been deleted: specifically, the differential and log backups that occur before the earliest full backup that is still on disk. Does anyone have a way of doing this (PowerShell script, perhaps)?

share|improve this question

migrated from stackoverflow.com Nov 13 '11 at 6:04

This question came from our site for professional and enthusiast programmers.

3 Answers 3

Here is a T-SQL script I wrote for my backup automation that deletes all but the specified number of full backups and any differential backups that were created before the oldest retained full backup. It relies on the differential having the same file extension and the file name having "_Diff" appended to it. I have one for log files as well.

Delete full/diff backups: http://www.sqlsoldier.com/wp/wp-content/uploads/Scripts/dba_DeleteDBBackups.sql Delete log backup: http://www.sqlsoldier.com/wp/wp-content/uploads/Scripts/dba_DeleteLogBackups.sql

share|improve this answer

If you create a Maintenance Plan, you can have Backup Tasks included in SubPlans. And in the SubPlan to backup the database (full, diff, tlog backups) you can have a Maintenance Cleanup Task. For each of the backup types, you can specify how long you want to retain the files. For instance, you can retain all backups for 7 days. After those 7 days, the baks and trns can be/will be deleted.

I believe this should achieve what you're after.

share|improve this answer
    
Not really. If I delete a full backup, the differentials and log backups that use that as a base are still taking up disk space. However, I still want to be able to restore up to a point in time using the remaining full backups and the differential and log backups in those chains. A simple "delete all backups of x type after n days" isn't going to accomplish that without leaving a lot of extra files on disk. –  Mark Nov 15 '11 at 21:59

I use a CLR proc for this, posted the code at http://sqlblog.com/blogs/merrill_aldrich/archive/2009/07/21/hole-in-your-backup-sequence.aspx

PowerShell might be better nowadays, but it'd be simple to port the C# code to PowerShell

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.