sample Powershell scripts for backup and restore sql server databases

backup script

Push-Location;Import-Module sqlps -DisableNameChecking;Pop-Location
$serverPath = "SQLSERVER:\SQL\mycomputer\SQL2012"
$databaseName = "payroll"
$backupTo = join-path (Get-Location) "$databaseName.bak"
Backup-SqlDatabase -Path $serverPath -Database $databaseName -Backupfile $backupTo

restore script

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