photo by analogicus

On one of my recent projects I was analysing my customer’s data for migration. Their legacy application could handle documents. They were stored on the organisation’s shared folder, and the file path was recorded in the database with the rest of data.

As a part of the gap analysis, I wanted to find out how many files were lost from the disk (I didn’t have any illusions that this has never happened). Obviously, this could only be done with PowerShell.

My task was to reverse what happened in this article, get a file path from the database, check if the file could be reached, and if not, add it to the result report of missing files. As I needed to check over a million files, I didn’t need to see the output on the screen, but rather it could be dumped into one resulting file.

The first thing I needed was a SQL Server module for PowerShell. It was a bit tricky as require admin rights. Otherwise, it’s easy enough to get from the PowerShell gallery.

From there it’s just good old ADO.NET logic: create connection object, open connection, define the command, execute the command and get the result into the data set, so it could be used.

PowerShell script

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=svr001;Integrated Security=true;Initial Catalog=TestDB”
$sqlConn.Open()
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn
$query = “SELECT file_path FROM dbo.tbl_files”
$sqlcmd.CommandText = $query
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null

$(foreach ($row in $data.Tables[0])
{
$r = Test-Path $row.file_path
$results = ''
if($r) {}
else {
$results = new-object psobject -property @{FilePath=$row.file_path}
$results }
}) | Export-csv "results.log" -NoTypeInformation

I found that only just over a hundred files were missing, which is a good rate in this case.

I like how PowerShell extends what you can do for data analysis and administration.

Kate
Data masseuse