I wrote a PowerShell script recently to actually accomplish a task I that I needed. What’s more, this was the first time I thought that Powershell might prove more useful than other methods. This series looks at my script, and this part examines the first part that I wrote.
I wrote an overview of this process, and then a script to loop through files. The next step is to connect to a SQL Server and loop through databases. I’ll also compare these against the MDF file names.
The first step is to connect to a database. First, I need to add a variable (eventually a parameter) that will hold the name of my instance.
$instance = ‘Tiny’
With this, I need to now open a SQL Server connection. I start with some assemblies I need. I saw a post that noted I need these assemblies. I’m not sure if I need them all, but this is where I started.
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null
The next step is to create a server object and connect. Note that as I add this code, I’ll run F5 to be sure things still work.
$server = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) $instance
That works fine, and now I need to see if I can get information from the server.
if ($debug -eq 2
{
“Database List”
“————-”
foreach($sqlDatabase in $Server.databases)
{ write-host “DB:” $sqlDatabase.name
}
#end debug
}
I add this right after the connection so that I get a list of databases. I use a new debug value so that I don’t see all files. What I expect is a list of databases, and then a list of mdf files (from the previous article).
This works by setting a variable to each of the items in the databases collection of the SQL Server instance. I then write out the name. When I do this, I see:
Success!
Now I want to alter this a bit more. I want to move this loop inside of the file loop. My plan is to take each file and use that to loop through each of the databases for a matching name.
However I’m not sure that a name match here is enough. What I want to note is if any of these MDF files are being used by SQL Server. Meaning that the MDF file is being used by one of the databases. To do that, I need to find the file and path of each database mdf file.
I do this by looping through each filegroup with this code. Note that I only worry about default filegroups. I’m torn on that, but it works for me.
$sqlfg = $sqlDatabase.FileGroups
foreach ($fg in $sqlfg| Where-Object {$_.ISDefault -eq $true})
Once I have this, I sub-loop inside this to check each of the files.
foreach ($dbfile in $fg.files | Where-Object {$_.ISPrimaryFile -eq $true} )
In here, I go through the files, pipe those to the Where-Object command and look for a property of ISPrimaryFile set to true. The result of this is run through the foreach loop. This gives me this code:
# loop through each of the databases
foreach($sqlDatabase in $Server.databases)
{
$sqlfg = $sqlDatabase.FileGroups
foreach ($fg in $sqlfg | Where-Object {$_.ISDefault -eq $true})
{
foreach ($dbfile in $fg.files | Where-Object {$_.ISPrimaryFile -eq $true} )
{
if ($debug -eq 4)
{
write-host “DB MDF File: ” $file.name
#end debug
}
#end foreach db file
}
#end foreach filegroup
}
# end foreach
}
and this result
That seems funny until you think about it. In this case, I’m taking the file and checking against each database, which means that I’m getting a loop inside a loop. Not the most efficient, but when this runs, it will be attaching these databases ones, so this should be OK.
What I want to do now is test if I get a match of the file. To do that, I need to get the full path. I’ll check to the FullName property to get the path and file. I then compare that to my mdf file with. Now I add an IF statement below my database loop.
if ($file.FullName -eq $dbfile.FileName)
{
if ($debug -eq 5)
{
write-host “Match ” $file.FullName ” = ” $dbfile.FileName
#end if
}
#end if
}
That gives me (with the proper debug value:
I can see my files (the first value) matching the file for my database (second value).
That concludes this post. At this point, I can tell what matches, The next step is to track those files that don’t match, and those will be the ones I attach. We’ll tackle that in the next post.
Voice of the DBA 