How to securely overwrite an existing database using the Powershell Restore-SqlDatabase cmdlet

Question

I got this cmdlet to restore a database, and it works fine if nothing is used using an existing database.

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

But rewriting fails when the database is used:

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

I could not understand if there was an easy way (via argument) to override this and kill the connections or put the database in single-user mode. Does something like this exist? or do I need to switch to SMO to do something more complex than basic recovery?

Now I am using a workaround to use the SMO API in order to reset the database first, but would like to simplify this if 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 am using Invoke-SqlCmd for it. Invoke-SqlCmd is part of the 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 

Further information on using Invoke-SqlCmd can be found here:

Effective use of Invoke-SqlCmd

SQL SERVER - FIX - Invoke-Sqlcmd term is not recognized as cmdlet name

Answer 2

Set the database to single user mode and roll back all existing transactions. This is done using the Tsql instruction, for example,

 ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

As you are already on it, why not get rid of the database before performing the restore?

 USE master; -- or some other db than the one you are going to drop DROP DATABASE MyDatabase; 

Another approach is to restore the database using the WITH REPLACE option.

Answer 3

There are no arguments in Restore-SqlDatabase for killing all processes that use the database that you want to restore.

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

 get-help Restore-SqlDatabase -full