--- title: "Find-DbaOrphanedFile" description: "Identifies database files on disk that are not attached to any SQL Server database instance" url: "https://dbatools.io/Find-DbaOrphanedFile/" availability: "Windows, Linux, macOS" tags: ["Orphan", "Database", "DatabaseFile", "Lookup"] author: "Sander Stad (@sqlstad), sqlstad.nl" source: "https://github.com/dataplat/dbatools/blob/master/public/Find-DbaOrphanedFile.ps1" last_updated: "2024-01-01" --- # Find-DbaOrphanedFile ## Synopsis Identifies database files on disk that are not attached to any SQL Server database instance ## Description Scans filesystem directories for database files (.mdf, .ldf, .ndf) that exist on disk but are not currently attached to the SQL Server instance. This is essential for cleanup operations after database drops, detaches, or failed restores that leave behind orphaned files consuming disk space. The command compares files found via xp_dirtree against sys.master_files to identify true orphans. By default, it searches the root\data directory, default data and log paths, system paths, and any directory currently used by attached databases. Perfect for storage cleanup scenarios where you need to reclaim disk space by identifying leftover database files that can be safely removed. You can specify additional file types using -FileType and additional search paths using -Path parameter. ## Syntax ```powershell Find-DbaOrphanedFile -SqlInstance [-SqlCredential ] [-Path ] [-FileType ] [-LocalOnly] [-EnableException] [-Recurse] [] Find-DbaOrphanedFile -SqlInstance [-SqlCredential ] [-Path ] [-FileType ] [-RemoteOnly] [-EnableException] [-Recurse] [] ``` ## Examples ### Example 1: Connects to sqlserver2014a, authenticating with Windows credentials, and searches for orphaned files ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sqlserver2014a ``` Connects to sqlserver2014a, authenticating with Windows credentials, and searches for orphaned files. Returns server name, local filename, and unc path to file. ### Example 2: Connects to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred ``` Connects to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file. ### Example 3: Finds the orphaned files in "E:\Dir1" and "E:Dir2" in addition to the default directories ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -Path 'E:\Dir1', 'E:\Dir2' ``` ### Example 4: Finds the orphaned files in "E:\Dir1" and any of its subdirectories in addition to the default directories ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -Path 'E:\Dir1' -Recurse ``` ### Example 5: Returns only the local file paths for orphaned files ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -LocalOnly ``` ### Example 6: Returns only the remote file path for orphaned files ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -RemoteOnly ``` ### Example 7: Finds the orphaned ending with ".fsf" and ".mld" in addition to the default filetypes ".mdf", ".ldf", ".ndf"... ```powershell PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014, sql2016 -FileType fsf, mld ``` Finds the orphaned ending with ".fsf" and ".mld" in addition to the default filetypes ".mdf", ".ldf", ".ndf" for both the servers sql2014 and sql2016. ### Required Parameters ##### -SqlInstance The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL Server version 2000 or higher. | Property | Value | | --- | --- | | Alias | | | Required | True | | Pipeline | true (ByValue) | | Default Value | | ### Optional Parameters ##### -SqlCredential Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -Path Specifies additional directories to search beyond the default SQL Server data and log paths. Use this when databases were stored in non-standard locations or when you suspect orphaned files exist in custom backup/restore directories. Accepts multiple paths and searches them alongside the automatically detected SQL Server directories. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -FileType Specifies additional file extensions to search for beyond the default database file types (mdf, ldf, ndf). Use this to find orphaned Full-Text catalog files (ftcat), backup files (bak, trn), or other SQL Server-related files. Do not include the dot when specifying extensions (use "bak" not ".bak"). | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -LocalOnly Returns only the local file paths without server or UNC information. Use this when you need simple file paths for scripting file removal operations or when working with a single server. Not recommended for multi-server environments since it omits which server the file belongs to. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -RemoteOnly Returns only the UNC network paths to orphaned files. Use this when you need to access files remotely for cleanup operations or when building scripts that run from a central management server. Provides the \\server\share\path format needed for remote file operations. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -EnableException By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -Recurse Searches all subdirectories within the specified paths in addition to the root directories. Use this when database files may be organized in nested folder structures or when conducting comprehensive cleanup of complex directory hierarchies. Without this switch, only the immediate directories are searched. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ## Outputs **System.String (when -LocalOnly is specified)** Returns the local file path to each orphaned file. System.String (when -RemoteOnly is specified) Returns the UNC path to each orphaned file. PSCustomObject (default) **Returns one object per orphaned file found with the following properties:** - ComputerName: The name of the computer where the SQL Server instance is running - InstanceName: The name of the SQL Server instance - SqlInstance: The full SQL Server instance name in the format ComputerName\InstanceName - Server: The server name (same as ComputerName for most instances) - Filename: The local file path to the orphaned file - RemoteFilename: The UNC network path to the orphaned file (\\ComputerName\share\path format) --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/