I have about 300 databases, (most) with a table, [File], and a column [Location] that holds a UNC path to a file. While migrating SANs, some needed files may not have been copied.

This issue materializes when a web GUI attempts to open a file from the table that doesn't exist. I've been trying to put together some PowerShell to iterate over the locations and run Test-Path, since that seems like the most direct way to perform a check.

I'm running into problems in two places:

I also need to check if the file exists in the old location, so I perform the REPLACE on [Location] to get the old SAN name in my query, but can't seem to parse the output correctly.

I would like to Copy-Item directly from what passes the test, but again, can't seem to parse the output correctly.

What am I missing? It's really the last two lines I can't figure out.

Code is below, and hacked together from an example HERE:

@('.SMO') |
     foreach-object {
    if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) 
           {"missing SMO component $MS$_"}
set-psdebug -strict
$ErrorActionPreference = "stop" # 

$My="$MS.Management.Smo" #
@("Instance1","Instance2", "Instance3") |
   foreach-object {new-object ("$My.Server") $_ } | 
    Where-Object {$_.ServerType -ne $null} | 
      Foreach-object {$_.Databases -match "DataPattern[0-9]"} | 
         Foreach-object { 
         $_.ExecuteWithResults(‘IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE TABLE_SCHEMA = ''schema'' AND TABLE_NAME = ''File'' AND COLUMN_NAME = ''Location'') SELECT [Location], REPLACE(Location, ''Computer'', ''Old-Computer'') [OldLocation] FROM [schema].[File]’).Tables[0]}|
                ForEach-Object {if(!(Test-Path -Path $_ ) -And (Test-Path -Path $_ -eq     True)) {
                    Copy-Item $_ $_ -ErrorAction Continue } }



Ended up getting it to work with the below two lines of code, but it's incredibly slow if I select anything more than the top 50 lines from each table. Any tips on speeding this up?

ForEach-Object {if(!(Test-Path -Path $_.Location ) -And (Test-Path -Path $_.OldLocation -eq True)) {
Copy-Item $_.OldLocation $_.Location -ErrorAction Continue } }  
  • Shouldn't this @("Instance1","Instance2", "Instance3") be @("server1\Instance1","server2\Instance2", "server1\Instance3") ?
    – Kin Shah
    Sep 10, 2014 at 19:53
  • It is in the actual code. This is anonymized a bit. Sep 10, 2014 at 19:56

1 Answer 1


PowerShell offers the ability to do things in many different ways, so this is just the method I would do in solving the problem at hand, based on the information provided.

SMO offers the ability to check the table class to more efficiently find the database in question that contain the table and column specified; instead of trying to use INFORMATION schema views that some folks have a case against ever using them.

I would break this up into sections that will make it more easy to debug. I am assuming you are not worried what UNC path is tied to which server, just making sure the UNC path exist and the file has been moved. So I would first pull all the UNC paths from any database I find the table and column exist.

#Load assembly

$serverList = 'server1','server2','server3\instance1','server3\instance2'

$searchTableName = 'File'
$searchColumnName = 'Location'

$uncPaths =
foreach ($s in $serverList)
    $srv = new-object microsoft.sqlserver.management.smo.server $s

    # The first "where" filters for the database with the specified table name
    # The second "where" filters for the column to exist within the table
    # The "-ExpandProperty Parent" is to go backwards in order to output just 
    #   the database name that contains the table and column
    $dbList = $srv.Databases | select -ExpandProperty Tables | 
        where {$_.name -match $searchTableName} | 
        select -ExpandProperty Columns | where {$_.name -match $searchColumnName} | 
        select -ExpandProperty Parent | select -ExpandProperty Parent |
        select -ExpandProperty name -unique

    # If $dbList count is greater than zero means it found a database
    if ($dbList.Count -gt 0)
        #now we need to pull the location paths
        foreach ($d in $dbList)
            $qry = "SELECT [Location] FROM $($searchTableName)"

            # The "-ExpandProperty" can also aid in outputting an object as just a string value
            Invoke-Sqlcmd -ServerInstance $s -Database $d -query $qry | select -ExpandProperty Location

# this variable should now contain all the UNC paths you need

Now in regards to changing the path to the old location I would do something like this when I am iterating through the $uncPaths variable:

foreach ($f in $uncPaths)
    $oldPath = $f.Replace("Current","Old")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.