by vmusunoor at 2013-05-06 08:26:17
Hi,by DonJ at 2013-05-06 12:00:56
i would like to replace the string in sql server output…I’m trying to pull database files information from a database…
Here’s my script…
[code2=powershell]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
}[/code2]
if you execute this …with parameters as
$clientname = sqlserver instance name
$DestinationDB = database name ( ofcourse i have hard coded master database in the above sql query )
You will get an output like below…
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
Now, i would like get the output with my destination db name…for example
if i give the parameters as
$clientname = testserver
$DestinationDB = Test
output should be like
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testlog.ldf
basically it must replace the master data and log file name.
Please let me know your suggestions…
Thanks in advance…
Use the -replace operator.
$string -replace "this","that"