How to backup multiple databases using Perl

Introduction

During the last 5 months, I have been learning Perl, so I decided to create a generic script to backup several databases, and be able to use it from other languages as well.

About the script:

The following script will create and execute a command line with the user provided options for the specified database in the option engine. The final result will be a backup file stored in the path you provided in the option dir.
In order to execute the script from a command line, you just need to create the command-line and define the required options, Example:

perl backup.pl utility="C:\Program Files (x86)\PostgreSQL\9.0\bin\pg_dump.exe" engine=postgres host=localhost port=5432 dbname=test user=dbuser password=user's password dir="D:\backup"

To backup another database, you just need to change the engine option and set the other options.

Script code

# -------------------------------------------------------------------------
# Package
#    backup.pl
#
# Purpose
#    Creates backup files for MySQL, SQL Server, Oracle and PostgreSQL
#
# Engineer
#    Carlos Luis Rojas Aragones 
#
# -------------------------------------------------------------------------
package backup;

# -------------------------------------------------------------------------
# Includes
# -------------------------------------------------------------------------
use utf8;
use strict;
use warnings;
use Getopt::Long;

# -------------------------------------------------------------------------
# Variables
# -------------------------------------------------------------------------
my $utility;
my $dbName;
my $host;
my $port;
my $dbEngine;
my $destinationPath;
my $user;
my $password;

# -------------------------------------------------------------------------
# Read Options
# -------------------------------------------------------------------------
GetOptions( 
    "utility=s"     => \$utility,
    "engine=s"      => \$dbEngine,
    "host=s"        => \$host,
    "port=i"        => \$port,
    "dbname=s"      => \$dbName,
    "user=s"        => \$user,
    "password=s"    => \$password,
    "dir=s"         => \$destinationPath,
) or die;

sub main{
    my @args = ();
    if($dbEngine eq "mysql"){
        @args = mysqlCommandLine();
    }elsif($dbEngine eq "sqlserver"){
        @args = sqlserverCommandLine();   
    }elsif($dbEngine eq "oracle"){
        @args = oracleCommandLine();
    }elsif($dbEngine eq "postgres"){
        @args = postgresCommandLine();
    }
    
    if(@args > 0){
        my $commandLine = createCommandLine(\@args);
        print $commandLine;
        my $result = qx{$commandLine};
        print $result;
    }else{
        print "Unable to backup the database";
        exit 1;
    }
    return;
}

# -------------------------------------------------------------------------
# Mysql
# -------------------------------------------------------------------------
sub mysqlCommandLine{
    my @args = ();
    
    if($utility && $utility ne ""){
        push(@args, '"' . $utility. '"');
    }
    
    if($host && $host ne ""){
        push(@args, qq{-h "$host"});
    }
    
    if($user && $user ne ''){
        push(@args, qq{-u "$user"});
    }
    
    if($password && $password ne ''){
        push(@args, qq{-p"$password"});
    }
    
    if($port && $port ne ''){
        push(@args, qq{-P $port});
    }
    
    if($dbName && $dbName ne ''){
        push(@args, $dbName);
    }
    
    if($destinationPath && $destinationPath ne ''){
        if(!($destinationPath =~ m/\.sql/)){
            $destinationPath .= ".sql";
        }
        push(@args, qq{> "$destinationPath"});
    }

    return @args;
}

# -------------------------------------------------------------------------
# Sql Server
# -------------------------------------------------------------------------
sub sqlserverCommandLine{
    my $backupStatement = "BACKUP DATABASE"; 
    my @args = ();
    
    if($utility && $utility ne ""){
        push(@args, '"' . $utility. '"');
    }
    if($user && $user ne ''){
        push(@args, qq{-U "$user"});
    }
    
    if($password && $password ne ''){
        push(@args, qq{-P "$password"});
    }
    
    if($dbName && $dbName ne ''){
        push(@args, qq{-d $dbName});
        $backupStatement .= " $dbName TO DISK = '";
    }
    
    if($host && $host ne ""){
        push(@args, qq{-S "$host"}); 
    }
    
    if($destinationPath && $destinationPath ne ''){
        if(!($destinationPath =~ m/\.dat/)){
            $destinationPath .= ".dat";
        }
        $backupStatement .= $destinationPath;
    }
    
    if($backupStatement && $backupStatement ne ''){
        push(@args, qq{-Q "$backupStatement'"});
    }
    
    return @args; 
}

# -------------------------------------------------------------------------
# Oracle
# -------------------------------------------------------------------------
sub oracleCommandLine{
    my @args = ();
    my $authString = "";
    if($utility && $utility ne ''){
        push(@args, '"' . $utility. '"');
    }
    
    if($user && $user ne ''){
        $authString = "userid=$user";
    }
    
    if($password && $password ne ''){
        if($host && $host ne ""){
            $authString .= "/$password@".$host;
        }else{
            $authString .= "/$password";
        }
    }
    
    if($authString && $authString ne ''){
        push(@args, $authString);
    }
    
    if($destinationPath && $destinationPath ne ''){
        # dump file
        if(!($destinationPath =~ m/\.dmp/)){
            $destinationPath .= ".dmp";
        }
        push(@args, qq{file='$destinationPath'});
        #log file
        $destinationPath =~ s/.dmp/.log/;
        push(@args,qq{log='$destinationPath'});
    }
    
    if($user && $user ne ''){
        push(@args, "owner=$user");
    }
    
    push(@args, qq{statistics="none"});
    
    return @args;
}

# -------------------------------------------------------------------------
# Postgres
# -------------------------------------------------------------------------
sub postgresCommandLine{
    my @args = ();
    
    if($utility && $utility ne ""){
        push(@args, '"' . $utility. '"');
    }
    
    if($host && $host ne ""){
        push(@args, qq{--host $host});
    }
    
    if($port && $port ne ''){
        push(@args, qq{--port $port});
    }
    
    if($user && $user ne ''){
        push(@args, qq{--username $user});
    }
    
    if($password && $password ne''){
        #we need to set the PGPASSWORD environment variable
        $ENV{'PGPASSWORD'} = $password;
    }
    
    push(@args, "--format custom --blobs --verbose");
    
    if($destinationPath && $destinationPath ne ''){
        if(!($destinationPath =~ m/\.backup/)){
            $destinationPath .= ".backup";

        }
        push(@args, qq{--file "$destinationPath"});
    }
    
    if($dbName && $dbName ne ''){
        push(@args, qq{"$dbName"});
    }
    
    return @args;
}

# -------------------------------------------------------------------------
# Returns a ready to execute command line
# -------------------------------------------------------------------------
sub createCommandLine {
    my ($arr) = @_;
    return join(" ", @$arr);
}

main();

1;

Parameters

I chose those options because they are "generic" and almost every database supports them; You could easily modify the script to provide special features.
The script can be called from a commandline and receives the following parameters:

1. utility: Absolute path to the back up utility (osql, exp, mysqldump or pgdump).
2. engine: Provide the database engine you want to use (mysql, sqlserver, oracle or postgres)
3. host: Database server name.
4. port: Database server port.
5. dbname: Name of the database you want to backup.
6. user: Database user.
7. password: User's password.
8. dir: Place where you want to store the backup file example: "D:\backup"

Using the script from C#

class BackUp
{
    public BackUp(){}

    public void CreateBackup(string scriptLocation, string args)
    {
        try
        {
            System.Diagnostics.ProcessStartInfo p = new System.Diagnostics.ProcessStartInfo(scriptLocation);
            p.Arguments = args;
            System.Diagnostics.Process proc = new System.Diagnostics.Process();
            proc.StartInfo = p;
            proc.Start();
            proc.WaitForExit();
        }
        catch (Exception e)
        {
            //handle the possible exceptions here
        }
    }
}

Using the class:

//options
string path             = Directory.GetCurrentDirectory() + "\\backup.pl";
string utility          = @"""C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe""";
string dbEngine         = "postgres";
string host             = "localhost";
int    port             = 5432;
string dbName           = "Test";
string destinationPath  = @"""D:\backup""";
string user             = "postgres";
string password         = "12345";

//create the args string
StringBuilder options = new StringBuilder();
options.Append("-utility=").Append(utility).Append(" ");
options.Append("-engine=").Append(dbEngine).Append(" ");
options.Append("-host=").Append(host).Append(" ");
options.Append("-port=").Append(port).Append(" ");
options.Append("-dbname=").Append(dbName).Append(" ");
options.Append("-user=").Append(user).Append(" ");
options.Append("-password=").Append(password).Append(" ");
options.Append("-dir=").Append(destinationPath).Append(" ");

//Instance the class we created before
BackUp oBackUp = new BackUp();
oBackUp.CreateBackup(path, options.ToString());

Conclusion:

Perl is a powerful language that allows us to create great things fast and easy, and it can run in the most popular operating systems.