Sql-server – Cannot see new drive in SSMS maintenance wizard

The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.

This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+. Just tested in 2012.

/*
Find Where DB Backups Went Physical Location
For last two days.  
backupset.type  
D --> FULL
I --> DIff or incrimental 
L --> Log backups
*/

DECLARE @dbname     sysname
SET @dbname = ''

SELECT  
     @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Date]
    ,msdb.dbo.backupset.user_name
    ,datediff(second, msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]    
    ,msdb.dbo.backupset.type
FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset 
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name  
WHERE 
    msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY    
    master.sys.sysdatabases.name
    ,msdb.dbo.backupset.backup_start_date 
    ,msdb.dbo.backupset.backup_finish_date
    ,msdb.dbo.backupmediafamily.physical_device_name
    ,msdb.dbo.backupset.type

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!