2

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)?

3 Answers 3

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

1

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.

1
  • 1
    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. Nov 15, 2011 at 21:59
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .