SQL Server 2016 – How to Move System Databases Using PowerShell

dbatoolspowershellsmosql serversql-server-2016

I'm trying to move the system databases using Powershell ie. without using any T-SQL.

  1. Using SMO :
    Install-Module SQL-SMO
    $smo = New-SMO -ServerName localhost
    $smo.databases["TempDB"].PrimaryFilePath= "F:\Tempdb\"
    $smo.databases["TempDB"].Alter()     gives an error:

    'PrimaryFilePath' is a ReadOnly property.

The logfile moves fine though.
$smo.databases["TempDB"].LogFiles[0].Filename = "F:\Tempdb\tempdb.ldf"

  1. Couldn't locate any specific cmdlets in the SQLServer or DBATools modules either. Copy-DbaDatabase comes closest; but not exactly.

Best Answer

While not completely answering your question about moving ALL system databases with Powershell, I did want to provide a working example of moving the individual files for TEMPDB.

Let's assume the current location of your TEMPDB files is

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA

and you want to move them to

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TempDB

You basically have to iterate through the filegroups and then the files within the filegroups.

Then, you do the same type of action against your log files.

Install-Module SQL-SMO
$smo = New-SMO -ServerName localhost
$TempDb = $smo.databases["TempDB"]

foreach ($fg in $TempDb.FileGroups) {
    foreach ($fl in $fg.Files) {
    $fl.FileName = $fl.FileName.Replace("C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA","C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TempDB")
    }
}

foreach ($fg in $TempDb.FileGroups) {
    foreach ($fl in $fg.Files) {
    $fl.FileName
    }
}

foreach ($fl in $TempDb.LogFiles) {
    $fl.FileName = $fl.FileName.Replace("C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA","C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TempDB")
    }

$smo.databases["TempDB"].Alter()

After restarting your SQL Server instance, you should see the TEMPDB files being allocated under the new directory.

After posting my original answer, I did a little more research on moving SQL Server system databases using POWERSHELL and found a very detailed post related to moving the master database.

Moving your Master Database with #Powershell.

Related Question