Find-DbaOrphanedFile

Want to see the source code for this command? Check out Find-DbaOrphanedFile on GitHub.
Want to see the Bill Of Health for this command? Check out Find-DbaOrphanedFile.

Synopsis

Find-DbaOrphanedFile finds orphaned database files. Orphaned database files are files not associated with any attached database.

Description

This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.

By default, it looks for orphaned .mdf, .ldf and .ndf files in the root\data directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.

You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.

Syntax

Find-DbaOrphanedFile -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Path <String[]>]
    [-FileType <String[]>]
    [-LocalOnly]
    [-EnableException]
    [-Recurse]
    [<CommonParameters>]

Find-DbaOrphanedFile -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Path <String[]>]
    [-FileType <String[]>]
    [-RemoteOnly]
    [-EnableException]
    [-Recurse]
    [<CommonParameters>]

Examples

Example: 1

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

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

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -Path 'E:\Dir1', 'E:\Dir2'

Finds the orphaned files in "E:\Dir1" and "E:Dir2" in addition to the default directories.

Example: 4

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -Path 'E:\Dir1' -Recurse

Finds the orphaned files in "E:\Dir1" and any of its subdirectories in addition to the default directories.

Example: 5

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -LocalOnly

Returns only the local file paths for orphaned files.

Example: 6

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -RemoteOnly

Returns only the remote file path for orphaned files.

Example: 7

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.

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.

-Path

Specifies one or more directories to search in addition to the default data and log directories.

-FileType

Specifies file extensions other than mdf, ldf and ndf to search for. Do not include the dot (".") when specifying the extension.

-LocalOnly

If this switch is enabled, only local filenames will be returned. Using this switch with multiple servers is not recommended since it does not return the associated server name.

-RemoteOnly

If this switch is enabled, only remote filenames will be returned.

-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.

-Recurse

If this switch is enabled, the command will search subdirectories of the Path parameter.