Powershell script to restore a SQL database from a backup file, with progress indicator.

restore.ps1

<#
  sqlps dependency
  If module sqlps does not exist, install from:
    Microsoft SQL Server 2016 Feature Pack (https://www.microsoft.com/en-us/download/details.aspx?id=52676)
    - SQLSysClrTypes.msi
    - SharedManagementObjects.msi
    - PowershellTools.msi
#>

param(
  [Parameter(Mandatory=$true,Position=0)]
  [string]$servername,
  [Parameter(Mandatory=$true,Position=1)]
  [string]$dbname,
  [Parameter(Mandatory=$true,Position=2)]
  [string]$filename)



Push-Location
Import-Module "sqlps" -DisableNameChecking
Pop-Location

<#
  MAIN
#>

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $filename, "FILE"
$restore.Devices.Add($device)
try {
  $filelist = $restore.ReadFileList($server)
}
catch {
	$exception = $_.Exception
	Write-Host "$exception. `n`nDoes the SQL Server service account have acccess to the backup location?" -ForegroundColor Red
  exit 1
}

$filestructure = @{}; $datastructure = @{}; $logstructure = @{}
$logfiles = $filelist | Where-Object {$_.Type -eq "L"}
$datafiles = $filelist | Where-Object {$_.Type -ne "L"}

# Data Files (if db has filestreams, make sure server has them enabled)
$defaultdata = $server.DefaultFile
$defaultlog = $server.DefaultLog
if ($defaultdata.Length -eq 0) {
    $defaultdata = $server.Information.MasterDBPath
}

if ($defaultlog.Length -eq 0) {
    $defaultlog = $server.Information.MasterDBLogPath
}

foreach ($file in $datafiles) {
	$newfilename = Split-Path $($file.PhysicalName) -leaf

	$datastructure.physical = "$defaultdata$newfilename"
	$datastructure.logical = $file.LogicalName
	$filestructure.add($file.LogicalName,$datastructure)
}

# Log Files
foreach ($file in $logfiles) {
	$newfilename = Split-Path $($file.PhysicalName) -leaf

	$logstructure.physical = "$defaultlog$newfilename"
	$logstructure.logical = $file.LogicalName
	$filestructure.add($file.LogicalName,$logstructure)
}

# Make sure big restores don't timeout
$server.ConnectionContext.StatementTimeout = 0

foreach	($file in $filestructure.values) {
	$movefile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
	$movefile.LogicalFileName = $file.logical
	$movefile.PhysicalFileName = $file.physical
	$null = $restore.RelocateFiles.Add($movefile)
}

Write-Host "Restoring $dbname to $servername" -ForegroundColor Yellow

# kill all connections
$server.KillAllProcesses($dbname)

try {

	$percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
		Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
	}
	$restore.add_PercentComplete($percent)
	$restore.PercentCompleteNotification = 1
	$restore.add_Complete($complete)
	$restore.ReplaceDatabase = $true
	$restore.Database = $dbname
	$restore.Action = "Database"
	$restore.NoRecovery = $false

  # take most recent backup set if there are more than one
  $restore.FileNumber = $restore.ReadBackupHeader($server).Rows.Count

	Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
	$restore.sqlrestore($servername)
	Write-Progress -id 1 -activity "Restoring $dbname to $servername" -status "Complete" -Completed

	Write-Host "Restore complete!" -ForegroundColor Green

  exit 0
}
catch {
	$exception = $_.Exception.InnerException
  Write-Host $exception -ForegroundColor Red
  exit 1
}

hi
when I use this script to restore database
cannot be overwritten. It is being used by database '*****'

Hi

what is the filename i have to use . Is it backup filename or logicalfilename of targetdb?

Kindly help me on this.