PowerShell to create databases from backup

Below is PS code snippet to restore many databases from one DB backup. Useful if we need to load test. There could be version mismatch issues if we directly use the RelocateFile class. So use the small hack as shown in the snippet.

---

1..100 | foreach { 

    $sqlServerSnapinVersion = (Get-Command Restore-SqlDatabase).ImplementingType.Assembly.GetName().Version.ToString() 

    $assemblySqlServerSmoExtendedFullName = "Microsoft.SqlServer.SmoExtended, Version=$sqlServerSnapinVersion, Culture=neutral, PublicKeyToken=89845dcd8080cc91" 

    $RelocateData = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('<Data file group name in backup>', "<data folder>\$_.mdf") 
    $RelocateLogs = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('<Log file group name inbackup>', "<Log folder>\$_.ldf") 
    $RelocateFG = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('<any more file group name eg File stream>',"<data folder>\$_") 

    Restore-SqlDatabase ` 
        -ServerInstance "<server name>\<instance>" ` 
        -Database "<db name>$_" ` 
        -BackupFile "<path to bak file>" ` 
        -RelocateFile @($RelocateData,$RelocateLogs,$RelocateFG) 

    "Restored $_ DB" 
}

---

Some points

• Change the value from 100 to increase the number of databases needed. 
• The new DBS will be created with the number suffixed to their names. 
• Relocating file is required. Else there could be collision with previous database.