Author Posts

January 10, 2016 at 2:47 am

I am trying to create a script that would backup DB on remote SQL server.
I am using Backup.properties for the parameters, the script looks fine but its still not working, could use some help please.

This is the SQL backup script:

param ([string]$BackupPath)

$sRawString = Get-Content ".\BackupRestore.properties" | Out-String
$sStringToConvert = $sRawString -replace '\\', '\\'
$htProperties = ConvertFrom-StringData $sStringToConvert

if ([string]::IsNullOrEmpty($BackupPath))
{
$BackupPath = $htProperties.'BackupDestination'
}

$timestamp = ((get-date).toString("yyyy_MM_dd_hh_mm"))
$file = ($BackupPath + "SQL_" + $timestamp + ".bak")

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ServerInstance = $htProperties.'sqlHost'
$connection.LoginSecure = $false
$connection.Login = $htProperties.'sqlUser'
$connection.Password = $htProperties.'sqlPass'

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $connection
$backup = New-Object Microsoft.SqlServer.Management.Smo.backup
$backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$backup.BackupSetDescription = "Full backup of Fox"
$backup.Database = $htProperties.'DBName'

$device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($file, 'File')
$device.DeviceType = 'File'
$device.Name = $file

$backup.MediaDescription = "Disk"
$backup.Database = $htProperties.'DBName'
$backup.Devices.Add($device)
$backup.SqlBackup($server)

appreciate the help 🙂
Nataly

January 10, 2016 at 3:06 am

My suspicion is that your problem is hidden somewhere in the properties you're using. Can you share the contents of BackupRestore.properties

I'd also look at stepping through the script one line at a time and checking that variables and properties are what you expect. I suspect something isn't being set correctly

January 10, 2016 at 3:42 am

thank you for the quick response 🙂

This is the relevant part of the file

#sql Host Name/IP: the fox DB server host name or IP or FQDN, this address should be accessible from
#where the BKS Advanced Installer Tool is executed. Also in some cases this host name will include
#instance name and port.
#Default Value: is the string value in registry KEY:
#HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BKS\Fox\\SQL_Server if key does not exists then
#it must be added in this file.
sqlHost=ALONE-LAP

#sql DB schema name: the fox data base name in SQL instance, the schema that fox application
#will connect to.
#Default Value: is the string value of registry KEY:
#HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BKS\Fox\\Sql_DataBase if key does not exists then
#it must be un commented and added in this file.
#Example: "Fox" or "FoxDB" etc... Without the double quotes
DBName=Fox8

#sql User Name: the SQL user name is the user name (Windows or SQL) that is used to authenticate
#with the SQL server for running the Fox update sql scripts. This parameter is used only when
#(WinAuthentication=False) or when (WinAuthentication=True and UseUserContext=True).
#Default Value: None
#Example: "FoxUser" without the double quotes
sqlUser=Fox

#sql Password: the SQL user password is the password for the user (Windows or SQL) that is used to
#authenticate with the SQL server for running the Fox update sql scripts.
#this parameter is used only when
#(WinAuthentication=False) or when (WinAuthentication=True and UseUserContext=True).
#Default Value: None
#Example: "Fox Password" without the double quotes
sqlPass=!QAZ2wsx

January 11, 2016 at 12:01 pm

I am not sure if you resolved your issue, but may I suggest (if possible) to try using the SQL PowerShell Module. There is a Backup-SqlDatabase cmdlet that would probably simplify your script and eliminate the need for a properties file. Again, I do not know what your requirements are for the backups, but look into it..