Backup SQL Server With Powershell

If you are working with Microsoft SQL Server, you may find yourself needing some sort of non-traditional backup. If that is the case you have come to the right place. Below we will detail how to backup SQL Server databases using a Powershell script. Note, this script can be extended to add email functionality by following our article Send Email With Powershell 2.

####################################################################
# SqlBackup.ps1
# Author: Matthew Marable
# Updated: January 08, 2013
# Description:
# Back up the SQL Server Databases
####################################################################

$assemblylist =
"Microsoft.SqlServer.ConnectionInfo",
"Microsoft.SqlServer.Management.Sdk.Sfc",
"Microsoft.SqlServer.SMO",
"Microsoft.SqlServer.SMOExtended"

foreach ($asm in $assemblylist)
{
    [void][Reflection.Assembly]::LoadWithPartialName($asm)
}

# Set server connection info
$conn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$conn.ServerInstance = "(local)"
$conn.LoginSecure = $true
# Set LoginSecure = $false and uncomment below to use sql auth
# $conn.Login = "sql_user"
# $conn.Password = "sql_pass"

# Connect to server
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $conn 

# Set databases to backup
$dbnames = @("PROD","DEVEL","EXPENSE")         

# Set backup location
Set-Location "C:\Data\SQL Backup\" 

# Backup databases
foreach ($db in $dbnames)
{
	# Move old backup
	if (Test-Path "$db.bak") { Move-Item "$db.bak" "$db-OLD.bak" -force }
	$backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
	$backup.Action = "Database"
	$backup.Database = $db
	$backup.Devices.AddDevice($db + ".bak", "File")
	# Starting backup process.
	$backup.SqlBackup($srv)
}  

# Set script location
Set-Location "C:\Admin Tools\"