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

.PARAMETER SqlInstance
The SQL Server instance. You must have sysadmin access and server version must be SQL Server version 2000 or higher.

.PARAMETER SqlCredential
Allows you to login to servers using SQL Logins instead of Windows Authentication (AKA Integrated or Trusted). To use:

$cred = Get-Credential, then pass this $cred to the -SqlCredential parameter.

Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials.

To connect as a different Windows user, run PowerShell as that user.

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

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

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

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

.PARAMETER Silent
If this switch is enabled, the internal messaging functions will be silenced.

# build the query string based on how many directories they want to enumerate$sql=$q1$sql+=$($PathList|Where-Object{$_-ne''}|ForEach-Object{"$([System.Environment]::Newline)$($q2 -Replace 'dirname', $_)"})$sql+=$query_files_sqlWrite-Message-LevelDebug-Message$sqlreturn$sql}functionGet-SqlFileStructure{param([Parameter(Mandatory=$true,Position=1)][Microsoft.SqlServer.Management.Smo.SqlSmoObject]$smoserver)if($smoserver.versionMajor-eq8){$sql="select filename from sysaltfiles"}else{$sql="select physical_name as filename from sys.master_files"}

# Add support for Full Text Catalogs in Sql Server 2005 and belowif($server.VersionMajor-lt10){$databaselist=$smoserver.Databases|Select-Object-propertyName,IsFullTextEnabledforeach($dbin$databaselist){if($db.IsFullTextEnabled-eq$false){continue}$database=$db.name$fttable=$null=$smoserver.Databases[$database].ExecuteWithResults('sp_help_fulltext_catalogs')foreach($ftcin$fttable.Tables[0].rows){$null=$ftfiletable.Rows.add($ftc.Path)}}}

process{foreach($instancein$SqlInstance){try{Write-Message-LevelVerbose-Message"Connecting to $instance"$server=Connect-SqlInstance-SqlInstance$instance-SqlCredential$sqlcredential}catch{Stop-Function-Message"Failure"-CategoryConnectionError-ErrorRecord$_-Target$instance-Continue}# Reset all the arrays$dirtreefiles=$valid=$paths=$matching=@()

# Get the default data and log directories from the instanceWrite-Message-LevelDebug-Message"Adding paths"$paths+=$server.RootDirectory+"\DATA"$paths+=Get-SqlDefaultPaths$serverdata$paths+=Get-SqlDefaultPaths$serverlog$paths+=$server.MasterDBPath$paths+=$server.MasterDBLogPath$paths+=$Path$paths=$paths|ForEach-Object{"$_".TrimEnd("\")}|Sort-Object|Get-Unique$sql=Get-SQLDirTreeQuery$paths$datatable=$server.Databases['master'].ExecuteWithResults($sql).Tables[0]