Using SMO to move tempdb

• JakobBindslet

  Old Hand

  Points: 355

  More actions

  October 17, 2014 at 4:49 am

  #99068

  I'm trying to improve my SQL Server Instance configuration scripts using SMO.

  This includes moving TempDB to a new location, ie. the equivalent of:

  ALTER DATABASE tempdb

  MODIFY FILE (NAME = tempdev, FILENAME = 'D:\NewTempDBhome\tempdb.mdf');

  GO

  ALTER DATABASE tempdb

  MODIFY FILE (NAME = templog, FILENAME = 'D:\NewTempDBhome\templog.ldf');

  GO

  However I cannot find a way to do this using SMO (in PowerShell).

  I have attempted to use something like the following:

  $smo = New-SMOconnection 'Win2012R2_A\SQL2014'

  $smo.databases["TempDB"].LogFiles[0].Filename = <New location>

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

  But it fails. I expected hoped to find some MoveMyDatabaseFile() method, but apparently non exist.

  Any suggestions?

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 17, 2014 at 5:30 am

  #1753100

  Hi Jako,

  Your third line has a typo - $smo.datases instead of $smo.databases which probably doesn't help.

  Try calling the Alter method at the database level rather than the file level:

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

  Remember you'll need to stop & start the SQL service for the change to tempdb to take effect.

  Cheers

  Gaz