replace string in path using powershell

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  May 7, 2013 at 7:57 am

  #293109

  Hi,

  i'm trying to get the default sql server data and log file path using the below script...

  param

  (

  $ClientName,

  $DESTINATIONDB

  )

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $DataSet = New-Object System.Data.DataSet

  $SqlConnection.ConnectionString = "Server = $ClientName; Database = '$DESTINATIONDB'; Integrated Security = True"

  $SqlCmd.CommandText = "select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and

  d.name = 'master'"

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter.SelectCommand = $SqlCmd

  $SqlAdapter.Fill($DataSet)|out-null

  foreach ($Row in $Dataset.Tables[0].Rows)

  {

  $dev = $($Row[0])

  $dev

  }

  i'm getting the output like this

  C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

  C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

  i would like to replace the name of master in the above path my paramter $destinationDB.

  Please let me know

  thanks in advance

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  May 8, 2013 at 6:09 am

  #1613296

  Replace

  $SqlCmd.CommandText = "select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and

  d.name = 'master'"

  With

  $SqlCmd.CommandText = "select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and

  d.name = '$DESTINATIONDB'"

  That should work.

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!