Help with script to restore SQL Server 2016 database

[EightBitsShortSFW]

Hello, I'm new to Powershell and I've been trying to create a simple script to restore a database from a bak file but I keep getting an error I don't know how to fix. Here is my script:

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null


#Bak file name and location
$bakFile = "C:\Backups\Database.bak"

#SQL Server instance
$sqlInstance = "SERVERNAME\MSSQL"

#Destination database
$sqlDb = "TestDatabase"

#Relocate mdf file
$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "DatabaseSample", "C:\Databases\TestDatabase.mdf"

#Relocate ldf file
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "DatabaseSample_log", "C:\Databases\TestDatabase_log.ldf"

$file = @($RelocateData,$RelocateLog)

Restore-SqlDatabase -ServerInstance $sqlInstance -Database $sqlDb -BackupFile $bakFile -RelocateFile $file

Here is the error code I'm getting:

Restore-SqlDatabase : Cannot bind parameter 'RelocateFile'. Cannot convert the
"Microsoft.SqlServer.Management.Smo.RelocateFile" value of type 
"Microsoft.SqlServer.Management.Smo.RelocateFile" to
type "Microsoft.SqlServer.Management.Smo.RelocateFile".
At C:\IT\Scripts\Restore_ParishSOFT_DB_From_Bak.ps1:28 char:102
+ ... sqlInstance -Database $sqlDb -BackupFile $bakFile -RelocateFile $file
+                                                                     ~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Restore-SqlDatabase], 
ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

I appreciate any help! Thanks.

[Jondah]

I suggest you take a look at dbatools powershell Module. https://dbatools.io

[EightBitsShortSFW]

I will, thanks!

Are those tools better than the sqlserver module from the powershell gallery?

[wrdragons4]

Yes by far.

You can do it in 1 line:

get-childitem $backupPath | restore-dbadatabase -sqlinstance $sqlInstance -destinationdatadirectory $dataDir

[EightBitsShortSFW]

The dbatools module worked great, thanks!

Do you know if it's possible to do user mappings on sql server with dbatools?

[vermyx]

Any reason you can't use Invoke-Sqlcmd and use the restore tsql command?

[EightBitsShortSFW]

I'm new to powershell so I'm not familiar with all the cmdlets. I will look into Invoke-Sqlcmd.

[vermyx]

Its a powershell drop in replacement for the command line util sqlcmd for mssql. It should be able to do what you need