How can I use Powershell or sql to check the existence of files from the file paths in a database table

What kind of test is this? A regular test with a schedule? What kind of process is this?

Basically the result of this kind of check is useless, cause it is async.

I want to test that the files actually exist after failed drive caused the data to be restored . So I want a list of file that do not exist in comparision to the file locations specified in the pathnames stored in the DB.

Are all these paths locally on one server ?

Or are they on separate servers ?


What are the column headers in the db for this particular table ?

These paths are local so my temp table will have a result like
E:\NAS\Store\2014\05\file1.txt

The only relevant column is pathnames which holds the last part of the result 2014\05\file1.txt

Something like this should work for you:

    $query="Select column from table"
    $connection = new-object system.data.sqlclient.sqlconnection( "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;”) 
    $adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
    $table = new-object system.data.datatable
    $adapter.Fill($table) | out-null
    $dirArray = @($table | select -ExpandProperty columnn)
    return @($dirArray)
    foreach ($dirname in $dirArray) 
    {
    if (!(Test-path $dirname))  {write-host "Directory matching $dirname is not present on computer ..." -fore Red}
    elseif (Test-Path $dirname) {write-host "Directory matching $dirname was found on computer ..." -fore Green}
    }
    $connection.close()

Select allOpen in new window

Hi becraig,

Thanks for this...I think I am getting there. The script work well but does not seem to test the output. See below my implementation

    $query="select pathnamesX from checkfiles "
    $connection = new-object system.data.sqlclient.sqlconnection( "Data Source=SQLLAB1;Initial Catalog=DBMAR1;Integrated Security=SSPI;”)
    $adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
    $table = new-object system.data.datatable
    $adapter.Fill($table) | out-null
    $dirArray = @($table | select -ExpandProperty pathnamesX)
    return @($dirArray)
    foreach ($dirname in $dirArray)
    {
    if (!(Test-path $dirname))  {write-host "Directory matching $dirname is not present on computer ..." -fore Red}
    elseif (Test-Path $dirname) {write-host "Directory matching $dirname was found on computer ..." -fore Green}
    }
    $connection.close()

Output

E:\NAS\Store\2008\04\24\2222\22.txt
E:\NAS\Store\2008\04\24\2222\23.doc
E:\NAS\Store\2008\04\24\2599\1005.doc
E:\NAS\Store\2008\04\24\2599\1002.doc


I am I missing something?

Thnaks again

SJ

Hmmm let's try moving the loop outside the sql block.

I cannot really test this at the moment unfortunately.

    $query="select pathnamesX from checkfiles "
    $connection = new-object system.data.sqlclient.sqlconnection( "Data Source=SQLLAB1;Initial Catalog=DBMAR1;Integrated Security=SSPI;”)
    $adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
    $table = new-object system.data.datatable
    $adapter.Fill($table) | out-null
    $dirArray = @($table | select -ExpandProperty pathnamesX)
    $connection.close()

$dirarray | % {
    if (!(Test-path$_))  {write-host "Directory matching $_ is not present on computer ..." -fore Red}
    elseif (Test-Path $_) {write-host "Directory matching $_ was found on computer ..." -fore Green}
}

Select allOpen in new window

Becraig,
Apologies for the delay in reponse due to illness..... the script works afer you moved the loop outside the SQL. This correctly lists all tested file locations. How can I change the  script output to only write when the file location is not found?

many thanks

SJ

Becraig,

thanks very much... thi is exactly what i need!!