Import SQLPS module and use it to backup and restore DB to different server

Question

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 it through 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 is, 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:

Error:

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?

Thanks

Answer 1

Your best approach would be to use dbaTools. Check it out! You can install from the PowerShell Gallery, though if you're running Windows 7 you may have to upgrade PowerShell to v3 or above (go for v5!) to make use of the PowerShell Gallery.

dbaTools has a single command for backing up and restoring DBs.

Here's an example from their documentation:

Copy-DbaDatabase -Source sqlserver2014a -Destination sqlcluster -Exclude Northwind, pubs, AdventureWorks -BackupRestore -NetworkShare \\\\fileshare\sql\migration

Hope that helps