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

Share your PowerShell scripts for Deployment Manager

Moderators: Mike Upton, justin.caldicott, csmith, chirayu, DavidSimner

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

Postby jtucker@inflexxion.com » Mon Jan 13, 2014 1:58 pm

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 D:\\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
}
jtucker@inflexxion.com
 
Posts: 12
Joined: Thu Jun 27, 2013 10:23 pm
Location: Boston

Return to Deployment Manager PowerShell Scripts

Who is online

Users browsing this forum: No registered users and 0 guests