Basic Powershell SQL Backup – 2008

#run using the sqlps minishell
#e.g. sqlps bu_AdHoc.ps1 -db Testing -ServerName DemoServer -dest C:\Backups

param([string]$db ,[string]$ServerName, [string]$dest);

$timestamp = Get-Date -format yyyy_MM_dd_HHmmss;

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName;

$backup = New-Object (“Microsoft.SqlServer.Management.Smo.Backup”);

$backup.Action = “Database”;
$backup.Database = $db;
$backup.Devices.AddDevice($Dest + “\” + $db + “_full_” + $timestamp + “.bak”, “File”);
$backup.BackupSetDescription = “Full backup of ” + $db + ” ” + $timestamp;
$backup.CompressionOption = 1;
$backup.CopyOnly = 1;

#write backup command to the console for review if needed
write-host $backup.script($srv);

$backup.SqlBackup($srv);