Using SMO to move tempdb

  • JakobBindslet

    SSC Veteran

    Points: 273

    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

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply