##############################################################################################################
# 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
}