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!

Target Process 2.16 Installation on Windows 7 and SQL Server 2005 Express

TargetProcess is a proprietary agile project management tool supports Scrum, Extreme Programming (XP). From the start of August, and with version 2.15 TargetProcess have support for Kanban. A Kanaban is new switch in Software Development from Agile to Lean – also known as JIT (Just-In-Time). For more about Kanban read writings of Michael Dubakov CEO of TargetProcess and Derick Bailey on Los Techies.

The notion of existence for TargetProcess (TP) I’ve had from Steve Bohlen excellent screencast series Autumn Of Agile (never finished, though). TP is just plain simple, user-friendly, fun to work with, and TP have Community Edition for 5 users pack for free. Community Edition version is more than enough for me and for many others.

The installation of TP is quite trivial even though I have had problems with IIS 7 Application Pool user on Windows 7 RC release, but with this final version, no problem, quick and smooth, of course only if prerequisites are satisfied.

I think TP is made with Spring.NET, uses NHibernate for ORM, I think NVelocity is templeate engine, and its user friendliness entirely comes from ExtJS.

Gathering Reuquirements

First we have to download TargetProcess Community Edition. It can only be through signup, and as I remember, you have to wait for TP reply mail one or more days. In mail is a link to download TP and also licence and good thing is, once signup, you’ll get all TP updates, revisiting this mailed link.

TP requirement is a SQL Server database either 2005 or 2008 and what is good Express version are supported but for Windows 7 we have to install SQL Server 2005 SP3, older, I think are not compatibile with Windows 7.

SQL Server 2005

TP have requirement for SQL Server to use SQL Server Authentication. It is no problem if you haven’t install SQL Server, you can use Mixed mode during installation and apply password for sa user, but if you have SQL Server installed, you have to do two things, easily done with SSMS Express (and use SP3 version for Windows 7) :

1. Log on to your SQL Server SSMS Express then right-click on your server (SQLEXPRESS), choose Properties. It popup the window where you have to choose Security and on Server Authetication click radio button SQL Server and Windows Authentication, save it, and it requires to restart the server – easily done through SQL Server Configuration Manager or Administrative Tools – Services
2. Enable sa user and give him a password – In your SSMS SQLEXPRESS instance click on Security node in tree view to expand, then expand Logins. Here is the sa user, right-click on him Properties in popup window first choose Status. sa user is disabled by default so in status there is Login and we have to click radio button Enabled, and when is sa user is enabled on General in popup window you have to set password for our sa user.

Note: It is not wise to use sa user in production or over Internet, this is only for testing and local purpose. For production you could create new user and give ownership on database created for TP and used through installation.

TP have a also requirement for .NET 3.5 SP1 to be installed and IIS 7 which is not installed by default in Windows 7 but can be easily done with Turn Windows features on or of (inside Programs in Control Panel). Where you have to choose (check) Internet Information Services. It is clever to check every feature inside except FTP Server (of course you can check it if you need it). It can all be done through Web Platform Installer, but for me it is better to do it manually!

Smooth installation

Now the installation of TP should be quick and smooth, and only thing you have to enter is Database server (.\SQLEXPRESS), and password entered during installation of SQL Server or added through Security/Logins. After install, you can run TP from your local Web Server with username admin and password admin.

First thing when you’re logged inside TP is to apply your given licence, choosing Admin -> Licence. If you have serial number you will choose Automatically or Manually if you have .lic file on your disk, and in People link in upper menu change the admin account password to whatever you want. Nice thing is, at your home page is a link to generate the sample project from which you can learn a lot.

Enjoy in TargetProcess because, it is a wonderful product. If you have some questions please ask and stay Lean and Agile!