[PowerShell] Import SQLPS module and use it to backup and restore DB to different DB server RRS feed

  • Question

  • Hi,

    I am running PS script from the client machine (Win7) that does not have SQLPS (Ms SQL Server Database Engine) installed.

    Due to this, I do <g class="gr_ gr_592 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="592" id="592">it through</g> importing a remote ps-session:

    1. Create remote PS-Session to the SQL Server

    $sourceSQLRemoteSession = New-PSSession -ComputerName $SQLServerHostname -Authentication negotiate -Credential $PSOCredentialObject

    2. Import the SQLPS module

    Import-PSSession -Session $sourceSQLRemoteSession -Module SQLPS -DisableNameChecking

    3. Run SQL Backup

    Backup-SqlDatabase -ServerInstance "destinationSQL\SQLInstance" -Database "blabla_db" -BackupFile "c:\blabla.bak" -Credential $PSOCredentialObject

    The thing <g class="gr_ gr_455 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" data-gr-id="455" id="455">is,</g> if I need to restore the DB to a different SQL database server, require me to use "RelocateFile" objects, for example:

    $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("uat_project1_db", "c:\SQLDATA\blabla.mdf")
    $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("uat_project1_db_Log", "c:\SQLDATA\blabla.ldf")
    Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.trn" -RelocateFile @($RelocateData,$RelocateLog)

    And when I run the new object, it does not seem to like it:


    New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.RelocateFile]: verify that the assembly containing this type is loaded.At line:1 char:17
    + ... ocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFil ...
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
        + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

    What's the best method to approach this?


    • Moved by Bill_Stewart Thursday, January 25, 2018 10:35 PM Abandoned/unreadable
    Monday, November 20, 2017 1:37 AM

All replies

  • Local assemblies are not available in remote sessions.  They are only available on the local system.


    Monday, November 20, 2017 2:03 AM
  • Thanks for your reply!

    I suspect, the only option is to install Ms SQL Server Database Engine on the client machine (Win7)?

    Monday, November 20, 2017 2:21 AM
  • The sql commands work remotely without remote invocation.  Take some time off and learn the basics of PowerShell.  FOr SQL post in the SQLServer forum.  They wrote the CmdLets and assemblies.


    Monday, November 20, 2017 3:16 AM