Creating a Maintenance Plan for a SQL Azure Database

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) or run the following to view the backup history:

First create this function - I'll just have it in msdb - which is not a good idea.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Then you can run the following

SELECT     
 sd.name AS database_name, 
 ISNULL(bs.backup_finish_date, 0) AS backup_date,
 ISNULL(CASE bs.[type] 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log' 
  END, 'NEVER') as type ,
 bm.physical_device_name, 
 dbo.fc_FileExists(bm.physical_device_name) as DoesFileExists,
 bs.backup_size / 1024 / 1024 AS backup_size, 
 DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS duration
 FROM master.dbo.sysdatabases AS sd 
 LEFT OUTER JOIN msdb..backupset AS bs ON sd.name = bs.database_name 
 LEFT OUTER JOIN msdb..backupmediafamily AS bm ON bm.media_set_id =     bs.media_set_id
  GROUP BY 
   sd.name, 
   bm.physical_device_name, 
   bs.backup_size / 1024 / 1024, 
   DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date),
   bs.backup_finish_date,
   bs.[type],
   dbo.fc_FileExists(filename)
 order by bs.backup_finish_date desc

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!