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

Question

I got this cmdlet to restore a database and it works fine if there is nothing using the existing database.

Restore-SqlDatabase -ServerInstance $databaseServerInstance -Database $database -BackupFile $backupLocation -ReplaceDatabase

But overwrite fails when the database is in use with:

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use

I couldn't figure out if there is an easy way (via an argument) to override this and kill connections or put the database in single user mode. is there such a thing? or do I have to switch to SMO to do anything more complicated than a basic restore?

I am now using a workaround of using SMO API to drop the DB first but would love to simplify this if its possible.

$srv = new-object Microsoft.SqlServer.Management.Smo.Server($databaseServerInstance)

# If the database exists then drop it otherwise Restore-SqlDatabase may fail if connections are open to it
if ($srv.Databases[$database] -ne $null)
{
    $srv.KillAllProcesses($database)
    $srv.KillDatabase($database)
}


Restore-SqlDatabase -ServerInstance $databaseServerInstance -Database $database -BackupFile $backupLocation -ReplaceDatabase

Answer 1

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