SQL Backup using Powershell

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Matt Howard Matt Howard 8 months, 2 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #33683
    Profile photo of Nataly Gilad
    Nataly Gilad
    Participant

    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

    #33684

    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

    #33685
    Profile photo of Nataly Gilad
    Nataly Gilad
    Participant

    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

    #33738
    Profile photo of Matt Howard
    Matt Howard
    Participant

    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..

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.