Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

How to backup multiple databases using Perl

, 7 Jan 2012
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Carlos Luis
Software Developer Avantica Technologies
Costa Rica Costa Rica
No Biography provided
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 7 Jan 2012
Article Copyright 2012 by Carlos Luis
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid