SQL Server Dump with Powershell for backup

The first database that I did something useful was MySQL. I must admit I like that RDBMS but one thing that struck me, was how easy it is to dump whole database to a file.

So, I made script and put it in cron (a linux based Task Scheduler). The compression of file was by 7zip, not very planetary known, but awesome and best compression I’ve found.

The script backup.sh looks like this:

#!/bin/bash

FILE="dump-`date +%Y-%m-%d`.sql"

mysqldump -u root -psecretpass -c -e -K -r $FILE databasename

7z a -t7z $FILE.7z $FILE -m0=BCJ2 -m1=LZMA:d23 -m2=LZMA:d19 -m3=LZMA:d19 -mb0:1 -mb0s1:2 -mb0s2:3

rm $FILE

So, since last two years I’m working mostly in SQL Server. I tried to find the way to create this kind of script. I really don’t like SQL Server backups (.bak). I was a very astonished, for there’s no way for fully dump the SQL Server Database. The only way I found is through SQLDumper. A small software for dumping only INSERTs. Tables creation are not included, I wondered why, but this was my only option!

I wanted to use Powershell for scripting, and transition of upper bash code to Powershell was a quite a journey to me, it wasn’t easy but after few hours of searching, reading powershell documentation, I finally did it.

The script backup.ps1 looks like this:

$FILE="Backup_$(Get-Date -format "yyyy-MM-dd_HH-mm").sql"

.\SQLDumper.Console /filename=.\Backup.xml

ren .\backup.sql "$FILE"

.\7z a -t7z "$FILE.7z" "$FILE" -m0=BCJ2 -m1=LZMA:d23 -m2=LZMA:d19 -m3=LZMA:d19 -mb0:1 -mb0s1:2 -mb0s2:3

rm "$FILE"

echo Done...

A quick walkthrough of the script:

1. Create variable $FILE to hold the name of file with current date and time, and sql file extension
2. With SQLDumper console application create dump of database INERTSs (a backup.sql file), through the SQLDumper XML Templates (Backup.xml)
3. Renaming backup.sql to meaningful name (from $FILE variable, with Date and Time of backup)
4. Compressing the file with 7zip
5. Remove the .sql file
6. Done…

SQLDumper

SQLDumper has a GUI and console application. To easily crate the SQLDumper XML template, for first time is needed to run GUI version. Though it can be done manually by writing XML.
Steps are very easy in GUI environment:

1. Connect to Database
2. choose tables/fields
3. choose the directory to put the dump file (in my case D:\backup)
4. file name for backup file (in my case backup.sql)
5. save this template by File->Save Template.

Note: be sure to use the 3+ version of SQLDumper, because the earlier release of SQLDumper console was very hard to use as script. I couldn’t run the command as scheduled script, because in some strange way SQLDumper had “Press any key to continue…” at the end of the script, and script was hanging there, waiting for key, and I didn’t know how to simulate key with powershell, so I used 3+ version, in which this strange behavior was removed.

Task Scheduler

Task Scheduler is very easy to use, so I’ll not going through it step by step. The only step I’ll go is that Task Scheduler need an Action, and for action is used “Start a program”. Since the powershell script can’t be referenced, there is need for usual batch (.bat) script.

The backup.bat script looks like this:

powershell -command "& 'D:\backup\backup.ps1' "

And also be sure that your scripts are Remotely signed to run. Beware this is a security risk, you should signed it with Certificate!

To remotely sign the powershell scripts use this command:

set-executionpolicy RemoteSigned

Hope it helps, if you have quetsions/modifications about the post, please do write!

PowerShell Prompt with Mercurial Status

I like Linux shell a lot, and like Rails commands, and I like PowerShell. It is about time that someone from Microsoft wakes up from GUI dream and give power to the command prompt.

I wanted to play a little with PowerShell and read one interesting blog from Mark Embling and his blog post named My Ideal Powershell Prompt with Git Integration.

My last blog post was about transition from Subversion to Git, but the day after I give chance to Mercurial and stay with it. It’s totally nonsensical to speak which is better, because they both are great. I think this is same like question about Rails vs. Django, Github vs. BitBucket.

I found myself in Mercurial, and it is (for me) easier to use in Windows environment. So in spirit of Mark Embling post, I’ll write my Ideal PowerShell Prompt for Mercurial or hg if you want.

I haven’t use PowerShell a lot, I like it because I can use Linux Shell commands ls, cp, … And never used/write PowerShell scripting. But I think I can read code and I can quick adapt in new code/language and find way to accomplish tasks but as many with a little help from Google

There are two scripts, one is PowerShell Profile script and another with functions to get mosto of hg status/branch, named:

• Microsoft.PowerShell_profile.ps1 (inside it calls the second one)
• hgutils.ps1 (functions for hg)

Because I have no time right now to explain all code, I urge you to read Mark Embling great blog post for more information.

Here is the code for Microsoft.PowerShell_profile.ps1:

# My preferred prompt for Powershell.
# Displays mercurial branch and stats when inside a mercurial repository.

# You can clone it by
# hg clone https://xajler@bitbucket.org/xajler/powershell-prompt-for-mercurial/
# And find source here:
# http://bitbucket.org/xajler/powershell-prompt-for-mercurial/src/

# including mercurial functions to use it for prompt
. (Resolve-Path D:/Documents/WindowsPowershell/hgutils.ps1)

function prompt {
	$path = ""
	$pathbits = ([string]$pwd).split("\", [System.StringSplitOptions]::RemoveEmptyEntries)
	if($pathbits.length -eq 1) {
		$path = $pathbits[0] + "\"
	} else {
		$path = $pathbits[$pathbits.length - 1]
	}
	$userLocation = '@ ' + $path
	$Host.UI.RawUi.WindowTitle = $userLocation
    Write-Host($userLocation) -nonewline -foregroundcolor Green       

   if (isCurrentDirectoryMercurialRepository) {
        $status = mercurialStatus
        $currentBranch = mercurialBranchName

        Write-Host(' [') -nonewline -foregroundcolor Yellow
        Write-Host($currentBranch) -nonewline -foregroundcolor Magenta
        Write-Host(' A' + $status["added"]) -nonewline -foregroundcolor Green
        Write-Host(' M' + $status["modified"]) -nonewline -foregroundcolor Yellow
        Write-Host(' D' + $status["deleted"]) -nonewline -foregroundcolor Cyan
        Write-Host(' !' + $status["missing"]) -nonewline -foregroundcolor Magenta
        Write-Host(' ?' + $status["untracked"]) -nonewline -foregroundcolor Red        

        Write-Host(']') -nonewline -foregroundcolor Yellow
    }    

	Write-Host('>') -nonewline -foregroundcolor Green
	return " "
}

And here is the code for hgutils.ps1:

# Mercurial (hg) functions
# Kornelije Sajler (http://learnaholic.me)
# Adopted from Git version of:
# Mark Embling (http://www.markembling.info/)

# You can clone it by
# hg clone https://xajler@bitbucket.org/xajler/powershell-prompt-for-mercurial/
# And find source here:
# http://bitbucket.org/xajler/powershell-prompt-for-mercurial/src/

# Is the current directory a Mercurial repository/working copy?
function isCurrentDirectoryMercurialRepository {
    if ((Test-Path ".hg") -eq $TRUE) {
        return $TRUE
    }

    # Test within parent dirs
    $checkIn = (Get-Item .).parent
    while ($checkIn -ne $NULL) {
        $pathToTest = $checkIn.fullname + '/.hg'
        if ((Test-Path $pathToTest) -eq $TRUE) {
            return $TRUE
        } else {
            $checkIn = $checkIn.parent
        }
    }

    return $FALSE
}

# Get the current branch
function mercurialBranchName {
    $currentBranch = ''
    hg branch | foreach {
        $currentBranch += $_
    }
   # Write-Host($currentBranch)
    return $currentBranch
}

# Extracts status details about the repo
function mercurialStatus {
    $untracked = 0
    $added = 0
    $modified = 0
    $deleted = 0
    $missing = 0

    $output = hg status

    #$branchbits = $output[0].Split(' ')
    #$branch = $branchbits[$branchbits.length - 1]

   # Write-Host($output)
    $output | foreach {
        if ($_ -match "^R") {
            $deleted += 1
        }
        elseif ($_ -match "^M") {
            $modified += 1
        }
        elseif ($_ -match "^A") {
            $added += 1
        }
        elseif ($_ -match "^\!") {
            $missing += 1
        }
        elseif ($_ -match "^\?") {
            $untracked += 1
        }
    }

    return @{"untracked" = $untracked;
             "added" = $added;
             "modified" = $modified;
             "deleted" = $deleted;
             "missing" = $missing}
}

I put scripts on great Mrecurail repository hosting BitBucket so you can clone it by command

hg clone https://xajler@bitbucket.org/xajler/powershell-prompt-for-mercurial/

And find it on this BitBucket page. Also have README if you don’t know how to use it.

I also use great Console2 to host my PowerShell, if you like my below screenshot in BitBucket repository you can also find Console settings xml (console.xml).

Sorry for any mistakes that made in this blog post, because I’m in hurry!!!

Hope you’ll find it useful and ask questions if you have it.