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:

    [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

Udemy course: Improve your productivity with PowerShell

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.

Leave a Reply

Your email address will not be published. Required fields are marked *