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.
The overview contains information about my strategy and breakdown, and this post looks at the first item.
When I decided I was going to use Powershell for this task, the first thing I decided to do was find all the MDF files in my folder. When I examined my folder, I saw lots of files.
Actually, my instance had no files, but I copied over all my mdf/ldf files (apart from system databases) from my original install to the \Data folder for my new instance. I had created these databases for various tests and experiments, and as such, they tended to use the default naming from SQL Server. This meant:
- The database name was used as the mdf file, i.e. the Baseball database has Baseball.mdf as the file.
- The log file is the database name with _log.ldf. As in Baseball_log.ldf.
To start the script, I began by noting I’d need some parameters for the script, as in the folder where the data was stored. I decided to start with a variable, which I can then turn into a parameter.
$folder = ‘D:\mssqlserver\MSSQL11.MSSQLSERVER\MSSQL\DATA’
$debug = 0
I include a “debug” variable that I can use to print out information if needed.
I started with the Get-ChildItem command using the folder. I can use this in a foreach loop to run through all the child items.
# loop through each of the file
foreach ($file in Get-ChildItem $folder)
{
# end for loop of files
}
Note that when I build these loops, I close the brackets first, and include a comment that helps me figure out where this item ends. Before I go further, I decided to start outputting information. I added a debug statement.
# loop through each of the file
foreach ($file in Get-ChildItem $folder)
{
#Debug
if ($debug -eq 1)
{
write-host $file.name
#end debug
}
# end for loop of files
}
This will output all the files in the folder. I can change the debug value to 1 and then I’ll get this output:
I see all my .mdf and .ldf files, along with my Filestream storage folders. Now I need to limit things to a specific type of file.
There’s an Extension property for the items in a folder that I can use. I’ll add that.
if ($file.Extension -eq ‘.mdf’)
{
if ($debug -eq 1)
{
write-host $file.name
#end debug
}
# end if
}
When I run this, I get this output, and immediately see a problem.
My output runs together. I need to differentiate which log output is being printed. I do that with a message before each file name.
write-host “MDF Files: ” + $file.name
With this added (and customized) for each debug message, I get this:
That gets me the list of MDF files. If I turn off debugging, and add just a print, I see just my MDF files.
That’s a good loop. I’m sure there are easier and shorter ways to do this, but this works well, and it gives me flexibility if I’d like to change to another extension.
This is also the basis of moving forward, where I’ll need to connect to SQL Server and check this list of files against the databases on the server.
Voice of the DBA 