Deleting other files during maintenance plan

Question

I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with it, except one Maintenance Cleanup Task:

This should be deleting all .7z archives (equivalent to .zip or .rar) which get accidentally left in the nightly backups directory (despite our best efforts, we keep ending up with some people leaving these files on the server...), but we still have old .7z files present in subdirectories (e.g. E:\Backups\foo\bar.7z ) after the maintenance plan runs.

There are no errors in the SQL Server Logs.

I suspect this is because the "Delete files of the following types: Backup files" option is restricting the file types which can be deleted. I've also tried changing that to "Maintenance Plan text reports" with the 7z extension, with no change.

Is it possible to have a maintenance plan delete arbitrary (non-SQL Server) files during a Maintenance Cleanup Task? If so, any idea what I'm doing wrong?

Answer 1

As per MS here

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.

By using the Maintenance Cleanup task, a package can remove the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete.

I have not deleted any other extension files via maintenance plans and seems from above that a non sql file may not be deleted via Maint'ce plans:

What we use is a VB script and schedule it via SQL agent job:

Select type as :- Type ActiveX scipt when you create the step for below code in SQL agent job:

On Error Resume Next   
    Dim fso, folder, files, sFolder, sFolderTarget     
    Set fso = CreateObject("Scripting.FileSystemObject")   

    'location of the  files 
    sFolder = "B:\FolderA\ABC\"

    Set folder = fso.GetFolder(sFolder)   
    Set files = folder.Files     

    'used for writing to textfile - generate report on files deleted 
    Const ForAppending = 8 

    'you need to create a folder named "scripts" for ease of file management &  
    'a file inside it named "LOG.txt" for delete activity logging 
    Set objFile = fso.OpenTextFile(sFolder & "\Report\LOG.txt", ForAppending) 

    objFile.Write "================================================================" & VBCRLF & VBCRLF 
    objFile.Write "                     FILE REPORT                " & VBCRLF 
    objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF 
    objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF 
    objFile.Write "================================================================" & VBCRLF  

    'iterate thru each of the files in the respective folder 
    For Each itemFiles In files  
       'retrieve complete path of file for the DeleteFile method and to extract  
            'file extension using the GetExtensionName method 
       a=sFolder & itemFiles.Name 

       'retrieve file extension  
       b = fso.GetExtensionName(a) 
           'check if the file extension is abc(whatever is the extension of you're file
           If uCase(b)="abc" Then 

               'check if the files are older than 1days 
               If DateDiff("d",itemFiles.DateCreated,Now()) >= 1 Then 

                   'Delete any old files to cleanup folder 
                   fso.DeleteFile a  
                   objFile.WriteLine "FILE DELETED: " & a 
               End If 
           End If 
    Next   

    objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

    objFile.Close 

    Set objFile = Nothing 
    Set fso = Nothing 
    Set folder = Nothing 
    Set files = Nothing

Note* Above scripts work such that you need to create folder Report within you're base folder location and within that place a file LOG.txt which captures or logs the details of files deleted by the process. You can use this additional feature which we use to track, else modify the script as per needs!

Answer 2

Even though, @KASQLDBA has answered the question, below will supplement his answer from a different perspective :

• Since you are using SQL Server 2012, you should use NATIVE COMPRESSION as opposed to using third party 7z. Backup compression can be enabled using :

  USE master;
  GO
  EXEC sp_configure 'backup compression default', '1';
  RECONFIGURE WITH OVERRIDE;
  

  If you compressed your backups via Native SQL compression, you won't be able to get much compression if you attempt to zip/7zip/rar the backup file.

• There are good reasons NOT to use Maintenance Plans. Best is to use Ola's backup solution (and Index maintenance solution as well). Ola's solution has cleanup time and compression parameters (along with other parameters) as well :