2

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.
3
  • 3
    "I'm trying to move the system databases using Powershell ie. without using any T-SQL." Why? May 8, 2018 at 23:00
  • 2
    @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. May 8, 2018 at 23:45
  • 1
    Yep. That's the good reason. May 12, 2018 at 1:11

3 Answers 3

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.

1

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.

1
  • Which property of the TempDB should one edit to move it? May 9, 2018 at 12:25
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.