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.

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"

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

score 1 · Answer 1 · answered May 09 '18 at 00:07

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.

Scott Hodgin - Retired · Accepted Answer · 2018-05-09T14:47:51.607

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.

score 0 · Answer 3 · answered May 12 '18 at 00:09

$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.

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

3 Answers3