How to reliably overwrite existing database using Powershell Restore-SqlDatabase cmdlet

I'm using Invoke-SqlCmd for it. Invoke-SqlCmd is part of sqlps module, which you can download from here:

Push-Location; Import-Module sqlps -DisableNameChecking; Pop-Location$serverPath = "SQLSERVER:\SQL\localhost\Default"$databaseName = "YOUR_DATABASE"$restoreFrom = join-path (Get-Location) "$databaseName-before.bak"$databasePath = join-path $serverPath "Databases\$databaseName"if(Test-Path $databasePath){    Invoke-SqlCmd "USE [master]; ALTER DATABASE [$databaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$databaseName]"}Restore-SqlDatabase -Path $serverPath -Database $databaseName -BackupFile $restoreFrom

You can find more information on how to use Invoke-SqlCmd here:

Effectively Using Invoke-SqlCmd

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

There is no argument in Restore-SqlDatabase to kill all processes that uses the database you want to restore.

You can execute the script below for a full documentation of arguments in Restore-SqlDatabase

get-help Restore-SqlDatabase -full

Years later I ran into the same problem, but wanted to stay in the SMO. The solution was to use the TerminationClause Enum documented on the MS page here.The termination enum is used in line:

$db.alter(1)

and utilises either:

0 --> (default) FailOnOpenTransactions1 --> RollbackTransactionsImmediately

Full solution would look like this:

$Instance = 'localhost'$dbName = 'DB_I_Want_To_Restore'$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ($Instance)$db = $server.Databases | ? {$_.Name -eq $dbName}$db.useraccess = 'Single'$server.KillAllProcesses($db)$db.alter(1)                                      Restore-SqlDatabase -ServerInstance $Instance -Database $dbName -BackupFile 'location_of_your_backup_file'