Check if SQL Server supports CompressionOption in PowerShell

2015/11/11

You have a PowerShell script that does a database backup using SMO API.
You want to have a compressed backup, in order to save space.
You use

$smoBackup = New-Object (“Microsoft.SqlServer.Management.Smo.Backup”)
$smoBackup.Action = “Database”
$smoBackup.BackupSetDescription = “Full Backup of ” + $dbName
$smoBackup.BackupSetName = $dbName + ” Backup”
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = “Disk”
$smoBackup.Devices.AddDevice($targetPath, “File”)
$smoBackup.CompressionOption = 1

Cool, right ?
Wrong !

You might get an error like
BACKUP DATABASE WITH COMPRESSION is not supported on Express Edition (64-bit).
BACKUP DATABASE is terminating abnormally.

This is because several SQL Server versions / editions do not support the CompressionOption, among which SQL Server Express is one of them.
In order to check if the SQL Server supports the backup compression option, use the following script:

$compressOpt = -1 # Means that CompressionOption is not present
try {
  # For the line below, you could also use -DisableNameChecking,
  # but in my case, inside Chef using PowerShell script, was crashing
  Import-Module ‘sqlps’ -EA 0 -WA 0 > $null
  # Check to see if we have indeed backup CompressionOption
  $compressOpt = (Invoke-Sqlcmd -Query “SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ‘backup compression default’),-1) As CompressionOption;” -ServerInstance $serverName).CompressionOption
}
catch {
}

if ($compressOpt -gt -1) { # Means that we can use backup comppresion
  $smoBackup.CompressionOption = “1”
}

Note 1: Credits go to StackOverflow, IT blogs and alike, sites that helped me with the code when searching for solutions.
Note 2: Be aware that when copy-pasting, the quotes and possibly other characters get messed up by WordPress, you’ll have to replace them.

Advertisements

FTP upload with ANT

2015/03/26

Problem: After a build, you may want to upload the deploy-able files to an FTP server.
Here’s how to upload, let’s say, some SQL update scripts to the FTP server, with ANT:

Ant Ftp Upload

We test to see if the property ftp.upload.sqlUpdates.folder is set; the value for it will contain the folder from where to do the upload of the SQL scripts; this property can be set in any property file that should be updated per each build.

Also, for this to work, you’ll need to add to your ANT library or MAVEN dependencies 2 new JAR files:
Jakarta Oro
Commons Net

Ant Ftp settings

As for the rest, the properties for the FTP (server, user, password, upload folder) can be declared in a simple property file, which should not be included in the final build.

Because I’m lazy and/or I don’t have time to format the code, above is a screenshot for clarity, below is the code for whoever needs it.

    <target name=”ftp-sql-upload.test.if.set”>
        <condition property=”ftp-sql-upload.is.set” value=”true” else=”false”>
            <not>
                <equals arg1=”${ftp.upload.sqlUpdates.folder}” arg2=””
                    forcestring=”true” />
            </not>
        </condition>
    </target>
    
    <target name=”ftp-sql-upload”
            depends=”ftp-sql-upload.test.if.set”
            if=”${ftp-sql-upload.is.set}”>
        <ftp
            server=”${ftp.upload.server}” remotedir=”${ftp.upload.remote.dir}”
            userid=”${ftp.upload.user}” password=”${ftp.upload.password}”
            action=”send” verbose=”yes” depends=”yes”>
            <fileset dir=”${ftp.upload.sqlUpdates.folder}”>
                <include name=”**/*.sql” />
            </fileset>
        </ftp>
    </target>

Note 1: Credits go to StackOverflow, IT blogs and alike, sites that helped me with the code when searching for solutions.
Note 2: Be aware that when copy-pasting, the quotes and possibly other characters get messed up by WordPress, you’ll have to replace them.


Get the last number from the end of a string in T-SQL

2013/08/09

If you have several rows in a table with a column containing text like ‘abc_123 / def_234 / ghi_345’, you can retrieve the 345 number with an SQL select like:

select reverse(substring(reverse(‘ghi_345’), 1, charindex(‘_’,
reverse(‘ghi_345’)) – 1))

%d bloggers like this: