gistfile1.ps1

# Restore a Database backup to the given database name
# providing the SQL .bak file to use. 
# Author: Patrick Magee

# TODO: Load password from external source.

Import-Module sqlps -Verbose

$databaseName = "SQL_DATABASE_NAME" # Local UAT Database Backup Name
$backupFile = "SQL_BACKUP_FILE.bak" # The file
$sqlInstance = "SQL_INSTANCE_NAME"
$username = "SQL_USERNAME"
$password = "SQL_PASSWORD"
$securePassword = $password | ConvertTo-SecureString -AsPlainText -Force

$credential = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securePassword

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME

$sqlServer.KillAllProcesses($databaseName)

$sqlQuery = "USE [master]; ALTER DATABASE [$databaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [$databaseName] SET ONLINE"

Invoke-Sqlcmd -Username $username -Password $password `
              -ServerInstance $serverInstance `
              -Query $sqlQuery `
              
write "Restoring $databaseName with backup: $backupFile "
write $credential 

$serverInstance = "$env:COMPUTERNAME\$sqlInstance"

Restore-SqlDatabase -ServerInstance $serverInstance `
                    -Database $databaseName `
                    -BackupFile $backupFile `
                    -Credential $credential `
                    -Confirm

write "Done restoring $databaseName."