InstallAdventureWorksDb.ps1

#works with SQL Server 2016 Express & Visual Studio 2015 Community

New-Item 'c:\temp' -ItemType Directory -Force 
Set-Location C:\temp
choco install 7zip.commandline --yes --force
wget https://msftdbprodsamples.codeplex.com/downloads/get/880661# -OutFile adventureworksdb.zip
7z e .\adventureworksdb.zip

#assuming SQL 2016
$env:PSModulePath = $env:PSModulePath + ";C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules"
Import-Module SQLPS

$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "AdventureWorks2014_Data", "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf"
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "AdventureWorks2014_Log", "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\AdventureWorks2014.ldf"

#Invoke-Sqlcmd -ServerInstance '.\sqlexpress' -Query "DROP DATABASE AdventureWorks2014"
Restore-SqlDatabase -ServerInstance localhost\sqlexpress -BackupFile C:\temp\AdventureWorks2014.bak -Database AdventureWorks2014 -RelocateFile @($RelocateData,$RelocateLog)


<# 
#steps to troubleshoot if it doesn't work
$locations = [appdomain]::CurrentDomain.GetAssemblies() | ? { $_.Location -like "*smo*" } | select Location
$assemblyPath = 'C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll'
$fullName = [System.Reflection.AssemblyName]::GetAssemblyName($assemblyPath).FullName
[System.Reflection.Assembly]::Load($fullName)

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks2014_Data", 
    "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorks2014.mdf") 
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks2014_Log", 
    "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\AdventureWorks2014.ldf")

RelocateData | Get-Member
[Microsoft.SqlServer.Management.Smo.RelocateFile].AssemblyQualifiedName
#>