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
    "I'm trying to move the system databases using Powershell ie. without using any T-SQL." Why? – David Browne - Microsoft May 8 '18 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. – Ayan Mullick May 8 '18 at 23:45
  • 1
    Yep. That's the good reason. – David Browne - Microsoft May 12 '18 at 1:11
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.

  • Which property of the TempDB should one edit to move it? – Ayan Mullick May 9 '18 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, privacy policy and cookie policy

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