Backup a database in a line of code from a web server.

jtucker@inflexxion.com

Prerequisite is installing SQLPS on a web server.
To install SQLPS on a web server...

First download and install the SQLSysClrTypes, SharedManagementObject.msi, and PowerShellTools.msi
http://blog.smu.edu/wis/2012/11/26/sql- ... ule-sqlps/

Use the default locations for all installs.
At this point you will have the SQLPS module available in the path:
"c:\program files\microsoft sql server\110\tools\Powershell\modules\SQLPS\sqlps"

Upgrade to Powershell 4. using the Windows update script. Windows6.1-KB2819745-x64-MultiPkg
(likely will require a reboot)
In a powershell script...

if ([string]::IsNullOrEmpty($SQLPowershellPath))
{
$SQLPowershellPath = "c:\program files\microsoft sql server\110\tools\Powershell\modules\SQLPS\sqlps"
}
$cur = Get-Location
import-module $SQLPowershellPath -DisableNameChecking
set-location $cur
# now for the powerful part.


Here is a sample backup database script - note we change the Red Gate Agent's service account to a windows account that has rights to perform
database backups.
# $ServerInstance is the address of the SQL Server instance such as 10.240.20.25\Sql2012
# $Database is the actual name of the database like "DynamicDeployment"
# $BackupDirectory is the path to the SQL Server's machine directory \RedGateDatabaseBackups (on 10.240.20.25)

function BackupDatabase($ServerInstance, $Database, $BackupDirectory)
{

$backupFile = "$BackupDirectory\$($Database).bak"
"Entering Powershell Library: BackupDatabase2012 with ServerInstance " + $ServerInstance + " Database " + $Database + " Backup directory " + $BackupDirectory + " Backup file " + $backupFile | Write-Host

"Query is" + "BACKUP DATABASE $Database to DISK='$backupFile' with INIT" | Write-Host

if ([string]::IsNullOrEmpty($SQLPowershellPath))
{
$SQLPowershellPath = "c:\program files\microsoft sql server\110\tools\Powershell\modules\SQLPS\sqlps"
}
$cur = Get-Location
import-module $SQLPowershellPath -DisableNameChecking
set-location $cur



Invoke-Sqlcmd -QueryTimeout 800 -Query "BACKUP DATABASE $Database to DISK='$backupFile' with INIT" -ServerInstance $ServerInstance
}