Comparing file name and view column in Powershell

• webrunner

  SSC-Dedicated

  Points: 31728

  More actions

  June 23, 2016 at 3:29 pm

  #327392

  Hello experts,

  I'm learning Powershell to try to accomplish a task. I say "learning" as a disclaimer - so if the code below is as ugly as I think it is, by all means let me know of a more elegant way!

  1. I am trying to use Powershell to query a view I have of database names.

  2. For each database in the view, I want to copy the latest backup file from production to staging. "DBSERVER" is production and "DBSERVERSTAGING" is staging.

  3. If a database name is not in the view, I do NOT want to copy that backup file.

  At the moment, I can get a list of databases parsed from the directory folders. I can also use Powershell to return a list of names from the view (see snippet below). I also have the code working to carry out the copy of the latest backup file from production to staging, using the code here as a starting point, since I use the Ola Hallengren backup solution:

  https://sqlactions.com/2015/03/30/powershell-script-to-manipulate-sql-server-backup-files/[/url]

  But when I try to combine the two, the Powershell script copies ALL the database backups from production, not just the subset in the view. I have commented out the copy line #Copy-Item $FileToCopy $DestinationPath -Include *.bak which is where I want the correct backup file copy to happen. And I am trying to filter out .trn files and use full .bak files only.

  So my basic question is: What is the most efficient way to use a database query/view as a basis (a loop I assume) for comparing the query/view values to the values parsed from a directory listing? Specifically, I have heard of Powershell arrays, so is there a way to output the query results into an array that I can then provide to the Copy-Item command in a loop?

  # Get database name from the file path.

  If ($FileToCopy.Length -gt 0 -And $FileToCopy.IndexOf("\FULL\") -gt 0)

  {

  $DatabaseName = $FileToCopy.SubString(($FileToCopy.IndexOf("\DBSERVER\") + 7),($FileToCopy.IndexOf("\FULL\") - $FileToCopy.IndexOf("\DBSERVER\") - 7))

  $DbDetails = "select name from dbo.db_list where name = '{0}'" -f $DatabaseName

  Invoke-Sqlcmd –ServerInstance DBSERVERSTAGING –Database dba –Query $DbDetails

  #Copy-Item $FileToCopy $DestinationPath -Include *.bak

  }

  Thanks for any help!

  - webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html