How can the System databases in SQL Server 2016 be moved using Powershell?

Question

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"

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

• David Browne - Microsoft: "I'm trying to move the system databases using Powershell ie. without using any T-SQL." Why?
• Ayan Mullick: @DavidBrowne-Microsoft , 1. The rest of my Azure Infrastructure deployment script is in Powershell. 2. I'm an IT Pro; not adept at T-SQL. 3. Microsoft is enabling Powershell to manage all its products.
• David Browne - Microsoft: Yep. That's the good reason.

Answer 1

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.

Answer 2

The Restore-DbaDatabase in dbatools right now blocks the ability to restore the system databases. It is on the roadmap at some point to work out the process. The block in that command is why Copy-DbaDatabase would fail as well, and you can't move any system database by simple detach/attach method.

Overall it could be worked out to perform the whole task in PowerShell using dbatools commands with a mix of just "manually prepared" code around SMO.

• Ayan Mullick: Which property of the TempDB should one edit to move it?

Answer 3

$smo = New-SMO -ServerName localhost -Verbose

$('model','MSDB','TempDB')| ForEach-Object {$Db = $smo.databases[$PSItem] foreach ($fg in $Db.FileGroups) {foreach ($fl in $fg.Files) {$fl.FileName = $fl.FileName.Replace("C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA","F:\SystemDB")}} foreach ($fl in $Db.LogFiles) {$fl.FileName = $fl.FileName.Replace("C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA","F:\SystemDB")} $smo.databases[$PSItem].Alter() }

Stop-Service -Name MSSQLSERVER -Force -Verbose

$('model','MSDB','mast')|ForEach-Object {Move-Item -Path $('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\'+$PSItem+'*') -Destination F:\SystemDB\ -Verbose}

$wmisvc = $(New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer 'localhost').Services | where {$_.name -eq "MSSQLSERVER"} $wmisvc.StartupParameters= '-dF:\SystemDB\master.mdf;-eF:\SystemDB\ERRORLOG;-lF:\SystemDB\mastlog.ldf' $wmisvc.Alter()

Start-Service -Name MSSQLSERVER,SQLSERVERAGENT -Verbose

This moves all the System databases.