4
Apr

## Restore Database with Move Files

More for me than you.  Something I type up all the time and forget where the heck I put it.  Not tested, but should give you the general idea.

```Import-Module SqlPS -DisableNameChecking

\$destServers = @('Server1', 'Server2', 'Server3')
\$srcPath = "\\sharepath\Backups"

try{

\$destServers | %{
\$serverName = \$_

\$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "\$serverName,1433"

gci -Path \$srcPath | ?{\$_.Extension -eq '.bak'} | %{
\$dbName = \$_.BaseName
\$backupFile = \$_.FullName

\$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem (\$backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

\$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
\$restore.NoRecovery = \$true;
\$restore.ReplaceDatabase = \$true
\$restore.Database = \$dbName

\$backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem (\$backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
\$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
\$moveFiles = @();
foreach(\$dbFile in \$dbFiles){
\$fileName = [System.IO.Path]::GetFileName(\$dbFile.PhysicalName);
switch(\$dbFile.Type){
'D'{
\$moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(\$dbFile.LogicalName, "E:\MSSQL\Data\\$fileName")
}
'L'{
\$moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(\$dbFile.LogicalName, "G:\MSSQL\TranLog\\$fileName")
}
}
}

\$srv.ConnectionContext.StatementTimeout = 0;
restore-sqldatabase -InputObject \$srv -Database \$dbName -BackupFile \$backupFile -RelocateFile \$moveFiles -ReplaceDatabase
}
}

}
catch{
\$_ | fl -Force
}

```