Okay, this is a task best suited for a PowerShell script that calls a T-SQL query. Here's a solution that combines both:
PowerShell Script (Find-OrphanedDbFiles.ps1)
[CmdletBinding()]
param(
[Parameter(Mandatory = $true, HelpMessage = "SQL Server instance name (e.g., 'SERVERNAME\SQLEXPRESS' or 'localhost').")]
[string]$SqlServerInstance,
Clarifying the Core Problem
I'm zeroing in on the crux of the issue: identifying MDF and LDF files that are truly "orphaned." That is, files residing on the system that aren't tied to any active database on a specific SQL Server instance. I'm focusing on clarifying this definition of "orphaned" so that any script built will provide the correct results.
Outlining Script Structure
I've mapped out the foundational structure for the script. My plan is to use PowerShell as the primary orchestrator, leveraging T-SQL to query the SQL Server instance. I'll execute a T-SQL query through PowerShell to get the list of "used" MDF/LDF files. Then, PowerShell will handle the file system operations, comparing the lists to pinpoint the orphans. I'm focusing on making the script flexible, allowing users to specify the SQL Server instance, and the folders to be scanned. I need to make sure the paths are handled correctly for compatibility.
| <domain type="kvm"> | |
| <name>HELIOS</name> | |
| <uuid>0ba9b4a2-e171-46e4-9b0d-633902566c7f</uuid> | |
| <metadata> | |
| <libosinfo:libosinfo xmlns:libosinfo="http://libosinfo.org/xmlns/libvirt/domain/1.0"> | |
| <libosinfo:os id="http://microsoft.com/win/11"/> | |
| </libosinfo:libosinfo> | |
| </metadata> | |
| <memory unit="KiB">50331648</memory> | |
| <currentMemory unit="KiB">50331648</currentMemory> |
| >nuget list Microsoft.Orleans -source http://nuget.smbsrc.net/ -prerelease -allversions | |
| Microsoft.Orleans.Client 1.1.0-at-0HW4NT | |
| Microsoft.Orleans.Client 1.1.0-beta-at-0HUK8T | |
| Microsoft.Orleans.Client 1.1.1-at-0JBP2P | |
| Microsoft.Orleans.Client 1.1.2-at-0JRHE5 | |
| Microsoft.Orleans.Client 1.1.3-at-0MB8M5 | |
| Microsoft.Orleans.Client 1.1.3-at-0NUQE1 | |
| Microsoft.Orleans.Client 1.1.3-at-0O4JEF | |
| Microsoft.Orleans.Client 1.1.3-at-0O4JJA | |
| Microsoft.Orleans.Client 1.2.0-at-0P3O5V |