TempDB move at include variable path

PowerShell Help
1

Hello,

We have hundreds of Channel Database on multiple SQL server, usually we are facing the space issue. Now decided to move tempdb location from C to E drive with CDB path name (E:\Program Files\MMSSQLServer\MSSQL12.\MSSQL\Data\Tempdb.mdf)

I am able to move the Tempdb with below script. BUT not able to set path passing variables in sqlquery:
QueryPath= “C:\SQLCMD\SQLQuery.sql”
$OutputFile = “C:\SQLCMD\QueryOutput.txt”
$ExecuteQuery= Get-Content -path $QueryPath | out-string

“Results – > `r`n`r`n” > $OutputFile

$server = import-csv “C:\SQLCMD\ServerList.csv”
$server | foreach-object {
$ServerName = $_.serverName
$CDB=$_ChDB

invoke-sqlcmd -ServerInstance $ServerName -query “$GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘$CDB’)
GO” -querytimeout 65534 | ft -autosize | out-string -width 4096 >> $OutputFile
}

GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘$CDB’)
GO"}

IT give me error as “invoke-sqlcmd : The path specified by E:\Program Files\MMSSQLServer\MSSQL12.\MSSQL\Data\Tempdb.mdf”
$ServerName -query “USE master”

I tried to pass variables using different method , but not work.

Please help

2

Afraid I can’t answer your specific query as to why this doesn’t work for you and I don’t have a SQL server to test against at the moment. However, you should take a look at dbatools.io.

Specifically, you want the Set-DbaTembDbConfiguration cmdlet.