Searching for orphaned DB files on the SQL server

##############################################################################################################
# Author: Martin Tydor
# Create date: 17/09/2020
# Description: PowerShell script for finding unnecessary DB files across the server drives.
# The result is the list of commands for removing the files which should be checked and
# executed in new PowerShell window.
# 
# Prerequisities:
# SQL server permissions for the listing DB files and Read/Delete permission on Filesystem is required.
#
#                          !!!Warning!!!
#
# List can contain DB files stored on the server by some purpose and should be checked before execution!
# Files you want to leave on the server should be removed from the list!
# The script will not work on SQL server with more than one SQL server instance!
# Execute script on your own risk and responsibility!
##############################################################################################################

#configuration part
$SQLServerPort = '1433' #SQL server port
[string[]]$Includes = @('*.MDF', '*.NDF', '*.LDF') #Filetypes wich should be found
$Drives ='[C]' #Exclude drives, e.g. [CDMP] - server will not look for files on that drives

#clear screen
cls

#getting data from DB
try {
    #Set datasource
    $dataSource = [System.Net.Dns]::GetHostByName($env:computerName).HostName+','+$SQLServerPort
    Write-Host "SQL server datasource: $dataSource"  -ForegroundColor green
    #Get DB files from SQL server
    $connectionString = “Server=$dataSource;Database=Master;Integrated Security=True;”
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

    $connection.Open()
    $resultsDataTable = New-Object System.Data.DataTable
    $command = $connection.CreateCommand()
    $query = “Create Table #temp (physical_name nvarchar(260));
        Exec sp_msforeachdb 'Use [?]; Insert Into #temp (physical_name) Select physical_name From sys.database_files';
        Insert Into #temp (physical_name)
        SELECT mf.physical_name FROM sys.databases db INNER JOIN sys.master_files mf ON db.database_id = mf.database_id 
except 
select physical_name from #temp;
        Select * From #temp
        drop table #temp”
    $command.CommandText = $query

    $result = $command.ExecuteReader()
    $resultsDataTable.Load($result) 

    $DBFilesArray = @()     
    foreach ($Row in $resultsDataTable.Rows){$DBFilesArray += @(,$($Row[0]).ToString())}

    $result.Dispose()
    $resultsDataTable.Dispose()
    $connection.Close()
 }
catch {
  Write-Host "An error occurred:"
  Write-Host $_
  Break
}

#search DB files on file system
$files =  get-psdrive -PSProvider "FileSystem" | Where-Object { $_.Name -notmatch $Drives} `
| % {get-childitem $_.Root -include $Includes –force -Recurse -ErrorAction SilentlyContinue | Where-Object { $_ -notin $DBFilesArray } } `
| Sort-Object -Property FullName 

#write count of all found files
Write-Host "Total unused DB files: $($files.Count)" -ForegroundColor green
Write-Host "Copy rows with files you want to remove and execute them in new PowerShell window" -ForegroundColor yellow
Write-Host "*******************************************************************************************"

#write found files
foreach ($file in $files) {
    Write-host "Remove-Item `"$file`"  " -nonewline 
    Write-host  "#$($file.Length)  bytes" -foreground yellow  
    Write-host  "#Updated:$($file.LastWriteTime)" -foreground yellow 
}