TempDB move at include variable path

Welcome Forums General PowerShell Q&A TempDB move at include variable path

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
9 months, 1 week ago.

  • Author
    Posts
  • #92753
    DJ

    Participant
    Points: 0
    Rank: Member

    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

  • #93121

    Participant
    Points: 4
    Rank: Member

    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.

The topic ‘TempDB move at include variable path’ is closed to new replies.