Having worked in busy dev-test environments, it isn’t uncommon to come across situations where someone detached a database from an SQL server, but forgot to reattach it, or drop it from the server. The next task we are then required to undertake, upon coming across such a situation, is to clean up the traces. This task can be complicated if the detached databases get deregistered from the SQL Server metadata catalog.
在繁忙的开发测试环境中工作后，遇到有人从SQL Server分离数据库但忘记重新附加数据库或将其从服务器删除的情况并不少见。 然后，在遇到这种情况时，我们需要执行的下一个任务是清理痕迹。 如果从SQL Server元数据目录中注销了分离的数据库，则此任务可能会很复杂。
Of course, there’s a way to track these using T-SQL. All we need to do is enable extended stored procedure (XP) and list all the files, and then compare them with the database files. Handling files using T-SQL is not the ideal approach, though. Also, running multi-database scripts can be cumbersome using T-SQL. So, how about using a PowerShell script to find out the detached databases on the local or remote drives of any database instance of any server?
当然，有一种方法可以使用T-SQL进行跟踪。 我们需要做的就是启用扩展存储过程（XP）并列出所有文件，然后将它们与数据库文件进行比较。 但是，使用T-SQL处理文件不是理想的方法。 同样，使用T-SQL运行多数据库脚本可能很麻烦。 因此，如何使用PowerShell脚本找出任何服务器的任何数据库实例的本地或远程驱动器上的分离数据库？
介绍 (Introduction)
In this article, we take the sledgehammer approach in searching for MDFs and then comparing them against files which are available on the listed drive. The idea behind this is to generate a report of detached database files across multiple servers. That’s one way to keep our databases compliant in terms of meeting SLAs, and ensuring optimal performance. Running such reports periodically helps us to achieve compliance in terms of file auditing.
在本文中，我们将使用大锤方法搜索MDF，然后将它们与列出的驱动器上可用的文件进行比较。 其背后的想法是生成跨多个服务器的分离数据库文件的报告。 这是保持数据库符合SLA并确保最佳性能的一种方法。 定期运行此类报告有助于我们实现文件审核方面的合规性。
At times, finding files seems like a necessary evil for IT pros; like finding a needing in a haystack. Imagine tens or hundreds of computers being the haystack! Is Get-ChildItem a good option? Sure, it works, but it could be considerably resource-intensive when working on an entire hard drive.
有时，查找文件似乎对IT专业人员来说是必不可少的。 就像在大海捞针中寻找需要。 想象一下，成千上万台计算机成为大海捞针！ Get-ChildItem是一个不错的选择吗？ 当然可以，但是在整个硬盘上工作时可能会占用大量资源。
Okay, so, the other way to go about it is to use the CIM_Datafile class of the WMI. This can be done using PowerShell 3.0 or higher. And as far as I know, WMI has the files registered as well. We would have to go about querying all the instances of the CIM_Datafile class. But it is as time-consuming as Get-ChildItem. If used wisely, though—like being specific with the query, with respect to the properties you look for—it can become an efficient and effective solution.
好的，另一种解决方法是使用WMI的CIM_Datafile类。 可以使用PowerShell 3.0或更高版本来完成。 据我所知，WMI也已注册文件。 我们将不得不查询CIM_Datafile类的所有实例。 但这和Get-ChildItem一样耗时。 但是，如果明智地使用它（例如针对查询特定于您要查找的属性），它可以成为一种有效的解决方案。
Let’s now look at the highlights of the script we would write to handle the task of finding such detached files:
现在，让我们看一下将要编写的脚本的要点，以处理查找此类分离文件的任务：
- Works across multiple servers 跨多台服务器工作
- Finds any detached file across multiple all instances on each server 在每个服务器上的多个所有实例中查找任何分离的文件
- Customized code to scan the drives 定制代码以扫描驱动器
- Use of credentials 凭证的使用
- Detailing the file details such as path, name and size 详细说明文件详细信息，例如路径，名称和大小
- And more… 和更多…
入门 (Getting started)
Step 1: Input parameter declaration
步骤1：输入参数声明
The parameters Inputfile, logfile, filtering parameter file-extension are defined and declared. The path of the log file is verified and recreated, if it exists.
定义并声明了参数Inputfile，logfile ，过滤参数file-extension 。 如果存在，则验证并重新创建日志文件的路径。
The input file should contain the list of the SQL instances which we’d like the report for. For example, the input file should look something like this:
输入文件应包含我们想要报告SQL实例的列表。 例如，输入文件应如下所示：
SQL1
SQL2
SQL3
SQL1
SQL2
SQL3
The following PowerShell script takes a text file with server names as input, and iterates through the list. It first tests of the server is reachable, and then lists out the active database files. The script also logs its progress in a separate log file. Database file extension and drive letters are used for filtering.
以下PowerShell脚本将带有服务器名称的文本文件作为输入，并在列表中进行迭代。 它首先测试服务器是否可以访问，然后列出活动的数据库文件。 该脚本还将其进度记录在一个单独的日志文件中。 数据库文件扩展名和驱动器号用于过滤。
The script takes in the username and the password, and stores these values in $Credentials. This variable is then passed as a parameter for Get-WmiObject.
该脚本接受用户名和密码，并将这些值存储在$ Credentials中。 然后将此变量作为Get-WmiObject的参数传递。
$instance='HQDBSP18'
$extension="mdf"
$User = 'abc\abcd'
$Pass = ConvertTo-SecureString 'SQLShack@2017' -AsPlainText -Force
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
#Connect to the given instance. Piping to Out-null to avoid showing loading echo in output
$drives=(get-wmiobject -class “Win32_LogicalDisk” -ComputerName $instance -Credential $Credentials) | ?{$_.drivetype -eq 3 -and $_.deviceID -eq "F:"} | foreach-object {$_.name}
#cycle over drives
foreach ($drive in $drives) {
$filter = "extension='$extension' AND Drive='$drive'"
Get-WmiObject -Class CIM_Datafile -Filter $filter -ComputerName $instance -Credential $Credentials|select name,FileName,@{Name="FileSizeMB";Expression={[math]::Round($_.FileSize/1MB,2)}} |Format-Table -AutoSize
}
Step 2: Loading SMO libraries
步骤2：加载SMO库
If you know the full assembly name, use the. Load() method.
如果您知道完整的程序集名称，请使用。 Load（）方法。
PS:>[reflection.assembly]::Load(“Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”)
PS：> [reflection.assembly] :: Load（“ Microsoft.SqlServer.Smo，版本= 13.0.0.0，文化=中性，PublicKeyToken = 89845dcd8080cc91”）
or
要么
PS:>Add-Type -Assembly “Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
PS：> Add-Type-程序集“ Microsoft.SqlServer.Smo，版本= 13.0.0.0，区域性=中性，PublicKeyToken = 89845dcd8080cc91”
Note: The LoadWithPartialName has been deprecated. The recommended solution now is to use the Add-Type or load a specific library.
注意：不推荐使用LoadWithPartialName。 现在推荐的解决方案是使用Add-Type或加载特定的库。
Step 3: Looping through each server to list active data files
步骤3：遍历每个服务器以列出活动数据文件
For each server, the connection is opened, and all the active data files are retrieved. The result set is prepared for step 4.
对于每个服务器，将打开连接，并检索所有活动数据文件。 结果集已准备好进行步骤4。
Step 4: List and compare the mdf files
步骤4：列出并比较mdf文件
This steps list the mdf files from the listed drives. The generated files are compared with the active data files.
此步骤列出了列出的驱动器中的mdf文件。 将生成的文件与活动数据文件进行比较。
It helps if we customize the drives. This way we can significantly reduce the traversing time of the query.
如果我们自定义驱动器，它会有所帮助。 这样我们可以大大减少查询的遍历时间。
Step 5: Write the output to a log file and to the console
步骤5：将输出写入日志文件和控制台
The complete PowerShell script is given in Appendix A.
完整的PowerShell脚本在附录A中给出。
示范 (Demonstration)
For the purpose of demonstration, we’ve created a new database called sqlShackDemo on two servers. After creating the databases, we’ll run a series of SQL statements to get the file locations, and detach the databases. SQLCMD is used to perform series of SQL operations.
为了进行演示，我们在两台服务器上创建了一个名为sqlShackDemo的新数据库。 创建数据库后，我们将运行一系列SQL语句以获取文件位置，并分离数据库。 SQLCMD用于执行一系列SQL操作。
:connect DBSP18
GO
SELECT @@Servername
GO
CREATE DATABASE SqlShackDemo;
GO
EXEC sp_helpdb N'SqlShackDemo';
GO
EXEC sp_detach_db 'SqlShackDemo', 'true';
GO
:connect spdbu13
GO
SELECT @@Servername
GO
CREATE DATABASE SqlShackDemo;
GO
EXEC sp_helpdb N'SqlShackDemo';
GO
EXEC sp_detach_db 'SqlShackDemo', 'true';
The output shows that the database SQLShackDemo is created. The sp_helpdb system stored procedure is used to generate the metadata for the database. sp_detachdb is used to detach the database from database engine.
输出显示已创建数据库SQLShackDemo。 sp_helpdb系统存储过程用于生成数据库的元数据。 sp_detachdb用于从数据库引擎分离数据库。
Input file
输入文件
The input file should have the names of the two database instances.
输入文件应具有两个数据库实例的名称。
There are, in fact, two ways to run the script:
实际上，有两种方法可以运行脚本：
- Modify the script, and save the file 修改脚本，然后保存文件
- Create a function and manually call the function by passing the required arguments 创建一个函数并通过传递必需的参数来手动调用该函数
The output shows the detached files from the system.
输出显示了与系统分离的文件。
结论 (Conclusion)
Orphaned database files eat up a lot of space in our environments.. Finding those file can be labor intensive. In this article, we discussed the different ways to find and handle such orphaned data files. One of the ways we’ve focused on is using a multi-server PowerShell script, which is a very efficient way of finding out the unattended files from the entire environment. Periodic generation of such detached reports comes in handy when cleaning up the system. This way, we can prevent many space-related problems on the servers. The script and the report it generates also helps us keep an eye on the detached files on each of the database instances we list in the input file, thereby ensuring that we’re compliant in terms of resource utilization, apart from maintaining space effectively.
孤立的数据库文件在我们的环境中占用了很多空间。查找这些文件可能会很费力。 在本文中，我们讨论了查找和处理这些孤立数据文件的不同方法。 我们关注的方法之一是使用多服务器PowerShell脚本，这是从整个环境中找出无人看管文件的非常有效的方法。 在清理系统时，定期生成此类分离的报告非常方便。 这样，我们可以防止服务器上发生许多与空间有关的问题。 脚本及其生成的报告还有助于我们关注输入文件中列出的每个数据库实例上的分离文件，从而确保我们在资源利用方面合规，除了有效地维护空间。
附录A (Appendix A)
Function Get-SQLUnattendedFile
{
<#
.Synopsis
The objective of the script is to make use of Input file in which it consists of list of database server instances as a source for various parts of the script.
.Description
This article is taking the sledge hammer approach and searching for mdf's and then comparing them against files which are available on the listed drive.
Function to log Output and display the details on the console.
.Parameter InputFile
Path to the file where the input details are saved.
Example: c:\InputServer.txt
.Parameter LogFile
The file logs all the information about the detached files or orphan file along with its size. This also contains the source of the server.
.Example
Write-Log -Message "$($Server) is reachable and starting the process " -Logfile $Logfile
.Example
Write-Log -Message "$(.Server) message " -Logfile $Logfile
.Example
Get-SQLUnattendedFile -inputfile c:\server.txt -logfile c:\DetachedFileList.txt
.Link
https://powershellsql.wordpress.com/ Jump
#>
[CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')]
Param(
[Parameter(Mandatory=$true,
Position=0)]
[String]$inputFile,
[Parameter(Mandatory=$true,
Position=1)]
[String]$logfile,
[Parameter(Mandatory=$true,
Position=2)]
[String]$extension='mdf'
)
#Prepare Log file
if (Test-Path $logFile) {
Remove-Item $logFile
}
$ErrorActionPreference = 'Stop'
$sqlservers = Get-Content $inputFile
# Prepare headers for the log file for each execution of script
Add-Content $logFile "#################################################################"
Add-Content $logFile "Unattended Database File Report"
Add-Content $logFile "Generated $(get-date)"
Add-Content $logFile "Generated from $(gc env:computername)"
Add-Content $logFile "#################################################################"
Function Write-Log {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$False)]
[ValidateSet("INFO","WARN","ERROR")]
[String]
$Level = "INFO",
[Parameter(Mandatory=$True)]
[string]
$Message,
[Parameter(Mandatory=$False)]
[string]
$logfile
)
$Stamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
$Line = "$Stamp $Level $Message"
If($logfile) {
Add-Content $logfile -Value $Line
}
Else {
Write-Output $Line
}
}
Try{
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
#for printing found instances data uncomment next line
#$Instances
foreach ($instance in $sqlservers)
{
If (!(Test-Connection $instance -count 1 -quiet)) {
Write-host "$($instance) is not reachable"
}
else
{
#Write the Progress to console
write-host "$($instance) is reachable and starting the process "
#Creating PowerShell custom objects
$colAttachedMDFs = @()
$files =@()
#Connect to the given instance. Piping to Out-null to avoid showing loading echo in output
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)
#get a list of all attached database file names
foreach ($db in $srv.Databases )
{
foreach ($fg in $db.Filegroups)
{
foreach ($file in $fg.Files)
{
#Adding to list of attached DBs
$colAttachedMDFs += $file.Filename
}
}
}
#select local logical drives
$drives=(get-wmiobject -class “Win32_LogicalDisk” -ComputerName $instance) | ?{$_.drivetype -eq 3 -and ($_.deviceID -eq "F:" -OR $_.deviceID -eq "G:" -OR $_.deviceID -eq "H:" -OR $_.deviceID -eq "I:" -OR $_.deviceID -eq "J:" )} | foreach-object {$_.name}
#cycle over drives
foreach ($drive in $drives)
{
$filter = "extension='$extension' AND Drive='$drive'"
$files +=Get-WmiObject -Class CIM_Datafile -Filter $filter -ComputerName $instance |select name,FileName,@{Name="FileSizeMB";Expression={[math]::Round($_.FileSize/1MB,2)}}
}
#$files
foreach ($mdf in $files)
{
if (-not ($colAttachedMDFs -contains $mdf.name))
{
#Adding to list of unattached DBs
$colMDFsToAttach += $mdf.name + [Environment]::NewLine
Write-Log -Message "On $($instance) -> The filename $($mdf.FileName) in this path $($mdf.name) with a size of $($mdf.fileSizeMB) MB is left unattended " -Logfile $Logfile
}
}
}
}
}
Catch{
#Catch error, rethrow and raise exit code
$_
}
$colMDFsToAttach
}
Get-SQLUnattendedFile -inputfile c:\server.txt -logfile c:\DetachedFileList.txt
Invoke-item c:\DetachedFileList.txt
翻译自: https://www.sqlshack.com/multi-server-script-find-orphaned-data-files-using-powershell/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
