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!

Port WordPress/Joomla from Apache to IIS 7

This WordPress blog as one Joomla website that I maintain, have been until now on Linux hosting and I decide it to port them to my Windows hosting provider. My Windows hosting provider of choice is Arvixe, I really like Arvixe because they are following new technologies, that I need. They even supporting .NET 4 Framework, ASP.NET MVC, Unlimited SQL Server database as olso unlimited MySQL 5 database.

So, how to port the widely known blog engine and CMS. It is very easy, because in my case Arvixe have installed Rewrite Module for IIS 7. Porting was easy, I just move the files from Linux server to Arvixe Windows server, and also create new database, dump old and import it to new, easily done with phpMyAdmin.

But, all the problem, is to port .htaccess and rewrite script in to the IIS Rewrite script. No big deal each site have scripts available, for the IIS (and Apache). In case of WordPress all is needed to put in web.config inside <system.webServer>:

<rewrite>
    <rules>
        <rule name="Main Rule" stopProcessing="true">
            <match url=".*" />
            <conditions logicalGrouping="MatchAll">
                <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
            </conditions>
            <action type="Rewrite" url="index.php" />
        </rule>
    </rules>
</rewrite>

And in case of Joomla, same thing but little different script and include disable chache for php:

<rewrite>
    <rules>
        <clear />
        <rule name="Common Exploit Blocking" stopProcessing="true">
            <match url="^(.*)$" />
            <conditions logicalGrouping="MatchAny">
                <add input="{QUERY_STRING}" pattern="mosConfig_[a-zA-Z_]{1,21}(=|\%3D)" />
                <add input="{QUERY_STRING}" pattern="base64_encode.*\(.*\)" />
                <add input="{QUERY_STRING}" pattern="(\&lt;|%3C).*script.*(\>|%3E)" />
                <add input="{QUERY_STRING}" pattern="GLOBALS(=|\[|\%[0-9A-Z]{0,2})" />
                <add input="{QUERY_STRING}" pattern="_REQUEST(=|\[|\%[0-9A-Z]{0,2})" />
            </conditions>
            <action type="Redirect" url="index.php" appendQueryString="false" redirectType="SeeOther" />
        </rule>
        <rule name="Joomla Search Rule" stopProcessing="true">
            <match url="(.*)" ignoreCase="true" />
            <conditions logicalGrouping="MatchAll">
                <add input="{URL}" pattern="^/search.php" ignoreCase="true" />
            </conditions>
            <action type="Rewrite" url="/index.php?option=com_content&amp;view=article&amp;id=4" />
        </rule>
        <rule name="Joomla Main Rewrite Rule" stopProcessing="true">
            <match url="(.*)" ignoreCase="true" />
            <conditions logicalGrouping="MatchAll">
                <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
                <add input="{URL}" pattern="(/|\.php|\.html|\.htm|\.feed|\.pdf|\.raw|/[^.]*)$" />
            </conditions>
            <action type="Rewrite" url="index.php/" />
        </rule>
    </rules>
</rewrite>
<caching>
    <profiles>
        <add extension=".php" policy="DisableCache" kernelCachePolicy="DisableCache" />
    </profiles>
</caching>

Hope it helps!

MySql Connector/NET 6.3.1 and Visual Studio 2010, what a mess

I just survived a real mess of installing latest alpha MySQL Connector for .NET (6.3.1), first when I downloaded it from, I think German mirror (SUNSite) there was no .msi file in mysql-connector-net-6.3.1.zip  only files like ones in “-noinstall” version. But after googling for an hour, found an .msi one version to download.

But, that is not all, running mysql.data.msi was not installing the product, it rollbacks the installation, with no error message at all. So I found one post on MySQL forum, one good soul has tried to debug Connector/Net on installation and found problem of “Config” folder in .NET Framework folders (x:\Windows\Microsoft.NET\Framework) .“Config” folder is only in CLR changed frameworks – 1.0, 1.1, 2.0, 4.0).  The problem was easily solved by renaming “Config” folder, for installation only, after renamed back to “Config”.

He (the guy from forum post) has problem with v1.1.43222, probably because he has one installed, I didn’t, so this wasn’t my problem, so I was stuck with this. But as I was reading through all replies (about 20+) to this post in forum, there was one reply, saying to rename all “Config” folder inside “Framework” folder.

This was like eureka to me, so I saw something interesting in “Framework” folder. Since I was following .NET 4 Framework from its early stages, I have had three “old” folders in it:

• Old_v4.0.20506
• Old_v4.0.21006
• Old_v4.0.301280

And all of this folders have “Config” folders. I renamed it all to “Config0”, and only one not renamed “Config” folders was:

• v2.0.50727
• v4.0.30319

Now, mysql.data.msi installs just fine and there are no more mess with Connector/.NET, and everything works fine from integration with Visual Studio 2010 to using MySQL with POCO feature in Entity Framework 4, now available with .NET 4 Framework only.

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.