Restore Microsoft SQL Server database using PowerShell

In previous post I wrote about the backup of Microsoft SQL Server database using PowerShell. Opposed operation to backup is restore, which can be executed using following PowerShell script:

param
(
    [string] $backupFile = $(throw "Backup file is required"),
    [string] $databaseName = $(throw "Database name is required")
)

$server = "."

Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance $server -Query "ALTER DATABASE $databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
Invoke-Sqlcmd -ServerInstance $server -Query "RESTORE DATABASE $databaseName FROM DISK='$backupFile' WITH REPLACE"
Invoke-Sqlcmd -ServerInstance $server -Query "ALTER DATABASE $databaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE"

At the first step database is set to single user mode. Next the restore of database is executed. After restore the database is set back to multi user mode.

If you are interested in PowerShell automation, take my Udemy course Improve your productivity with PowerShell.