Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

replace string in path using powershell Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 7:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:08 AM
Points: 94, Visits: 770
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
Post #1450153
Posted Wednesday, May 8, 2013 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 5,576, Visits: 3,426
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!!!
Post #1450517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse