Backup Microsoft SQL Server database using PowerShell

During development we need often create backup of Microsoft SQL Server database. Performing this task using SSMS UI dialog is slow and time consuming activity. This task can be automatized by following PowerShell script:

param
(
    [string] $databaseName = $(throw "Database name is required"),
    [string] $backupToDir = ".",
    [bool] $shrinkLogFile = $true
)

Import-Module SQLPS

if ($backupToDir -eq ".")
{
    $backupToDir = (Get-Item .).FullName + "\Backups\"

    if (-not (Test-Path $backupToDir))
    {
        New-Item -Path $backupToDir -ItemType Directory | Out-Null
    }
}

if ($shrinkLogFile)
{
    $shrinkCommand = "DBCC SHRINKFILE (" + $databaseName + "_log, 0)"
    Invoke-Sqlcmd -ServerInstance . -Database $databaseName -Query $shrinkCommand | Out-Null
}

$now = (Get-date).ToString("yyyyMMddHHmmss")
$backupCommand = "BACKUP DATABASE " + $databaseName + " TO DISK='" + $backupToDir + $databaseName + "_" + $now + ".bak' WITH COMPRESSION"

Invoke-Sqlcmd -ServerInstance . -Query $backupCommand

The script shrinks database log file and backup is created with compression to decrease the size of backup file.

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