Powershell Backup and Restore Sql Server Database

Restore-SqlBackup.ps1

# 
# Example
# Restore-SqlBackup -serverInstance TARDIS\SQLEXPRESS -backupFile C:\DATA\TestDatabase.bak -targetDatabase TestDatabaseClone
#
function Restore-SqlBackup($serverInstance, $backupFile, $targetDatabase) {
  $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance;

  $dataFolder = $server.Settings.DefaultFile;
  $logFolder = $server.Settings.DefaultLog;

  if ($dataFolder.Length -eq 0) {
    $dataFolder = $server.Information.MasterDBPath;
  }
  if ($logFolder.Length -eq 0) {
    $logFolder = $server.Information.MasterDBLogPath;
  }

  $backupDeviceItem = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $backupFile, 'File';
  $restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore';
  $restore.Database = $targetDatabase;
  $restore.Devices.Add($backupDeviceItem);

  $dataFileNumber = 0;

  Write-Information "Restoring backup $backupFile to $targetDatabase on $serverInstance"

  foreach ($file in $restore.ReadFileList($server)) {
    $relocateFile = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile';
    $relocateFile.LogicalFileName = $file.LogicalName;

    if ($file.Type -eq 'D') {
      if($dataFileNumber -ge 1) {
        $suffix = "_$dataFileNumber";
      }
      else {
        $suffix = $null;
      }
      $relocateFile.PhysicalFileName = "$dataFolder\$targetDatabase$suffix.mdf";
      $dataFileNumber ++;
    }
    else {
      $relocateFile.PhysicalFileName = "$logFolder\$targetDatabase.ldf";
    }

    $restore.RelocateFiles.Add($relocateFile) | Out-Null;
  }    

  $restore.SqlRestore($server);
  Write-Information "Database restoration successful"  
}