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!

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.

CakePHP: User Authetication

It is time for some coding. The first MVC Framework is CakePHP, because the final application for my firm will be in PHP. I will not write about installing and configuring CakePHP and I think that readers have notion of MVC Frameworks and how they work.

System requirements for CakePHP:

• PHP 4.3.2+ (PHP 5 recommended)
• Apache Web Server with mod_rewrite enabled (I recommend installing XAMPP)
• Supported Database – MySQL in my case

CakePHP Folder Structure

Before begin there is need for explanation of CakePHP structure. When you download cake and extract it you should get this folder structure:

• app – the folder where our application code shall reside.
• cake – core CakePHP files, where magic resides.
• vendors – where to place third-party PHP libraries you need to use with your CakePHP applications.

The app directory is only relevant to this application and it has this structure:

• config – database connection details, bootstrapping, core configuration files should be stored here.
• controllers – contains your application’s controllers and their components.
• locale – stores string files for internationalization.
• models – contains your application’s models, behaviors, and datasources.
• plugins – contains plugin packages.
• tests – unit test for the application.
• tmp – this is where CakePHP stores temporary data.
• vendors – any third-party classes or libraries should be placed here.
• views – presentational files are placed here: elements, error pages, helpers, layouts, and view files.
• webroot - in a production setup, this folder should serve as the document root for your application. Folders here also serve as holding places for CSS stylesheets, images, and JavaScript files.

So, when I post source for this application it’ll be only app directory!

But, before we begin, you should create a MySQL database in my case “cake” and create the users table with this script:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(8),
  `password` varchar(64),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

We also need to tell CakePHP where to find database. In app/config there is a file database.php.default which should be renamed in to database.php and add database information, in my case:

<?php
class DATABASE_CONFIG
{
	var $default = array(
		'driver' => 'mysql',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'root',
		'password' => 'lida',
		'database' => 'cake',
		'prefix' => '',
	);
}
?>

Note:  Here is only default configuration for database, test configuration is not important for now!

Authentication

CakePHP in its Core Components has a Authentication – AuthComponent is used for creating authentication easily and quickly. The components in cake are added to the list of components in your controller. Since we want to use authentication component for all of our controllers we shall create AppController as our global controller for application:

<?php
class AppController extends Controller
{
    var $components = array('Auth');
}
?>

Note: The AppController is not added to the app/controller directory where controllers reside, instead it is in root of app directory.

User Model

Since user as entity is for its self, model is very simple. It consists of $name attribute, set to the controller name. This is only important for PHP 4, even though I use PHP 5, we shall declare the $name attribute only.

Users Controller

The user controller is where our actions reside. We have this actions for user:

• index – only redirects to a login action/page.
• register - serves only for registration and to put some users to test login. Totally insecure and will not be a part of application. Password will be given to our users, and there will be no registration available!
• login – authenticate the user and if  Username/Password input is valid, redirects user to the main page.
• logout – logout the user from application.

Because we add AuthComponent to AppController, then authentication is for all application. In user controller we add this snippet of code:

function beforeFilter()
{
	$this->Auth->allow('register');
}

The Before Filter is added to allow registration of users, and this is only for test!

Login

function login()
{
	if(isset($this->data['User']))
	{
		if($this->Auth->login($this->data['User']))
		{
			$this->redirect('/');
		}
		$this->flash("Username/Password is incorrect");
	}
}

Logout

function logout()
{
	$this->Auth->logout();
	/* $this->flash('You are now logged out.'); */
	$this->redirect('login');
}

This code is very straight forward and it explain itself.

User Views – Presentation

First and foremost the views are inside the app/views/{NameOfController} folder in our case the folder is users. The extension for CakePHP view (presentation) files is .ctp and they are with name of controller actions. Since index and logout actions have no reason to have presentation files we have only presentation for register and login actions. But first we have to include some presentational helpers to ease our working with HTML and Forms. Helpers are included within each controller. To use presentational helpers for user views we have to add this line of code to our UserController:

var $helpers = array('Html', 'Form');</pre>
<h3>Register.ctp</h3>
<pre class="brush:php"><?php
echo $form->create('User', array('action' => 'register'));
echo $form->input('username');
echo $form->input('password');
echo $form->input('password_confirm', array('type' => 'password'));
echo $form->submit('Register');
echo $form->end();
?></pre>
<h3>Login.ctp</h3>
<pre class="brush:php"><?php
$session->flash('auth');
echo $form->create('User', array('action' => 'login'));
echo $form->inputs(array(
	'legend' => __('Login', true),
	'username',
	'password'
));
echo $form->end('Login');
?>

Both of views uses entirely helpers, which are also very self-explanatory.

We only have to test our login and logout and for that we need to have main page. Since we don’t want to create Home Controller and Index action we can add in our app/views/pages, a page home.ctp, which will be our home page when we call our domain. And all we have to do is put the logout link:

<?php
echo $html->link('Logout', array('controller' => 'users', 'action'=>'logout'));
?>

I think that would be all for User Authentication, and bear in mind to remove this insecure registration. It is only for testing and easily adding users to database. Next, we shall start to add strong entities like Clients and Voucher Types. Since then if you have any question please, ask or go to the manual of CakePHP for more informations.

Source code for this post: app.zip

Installing Ruby 1.9.1, Rails and mysql-ruby 2.8.1 on Windows 7 RC and MySQL 5.0

This is the short story or even a saga of all rises and falls during installation of Ruby 1.9.1, latest Rails and frustration to get MySQL work with Ruby/Rails via mysql-ruby Gem.

The Vision

I really haven’t been Ruby/Rails coding since 2007. And last weekend I just got frustrated with .NET MVC, so I started to switch to Ruby/Rails. Mainly, I was interested in Ruby 1.9 new features, so logically I tried to install Ruby 1.9. Since I got no Mac (sorry to say, but is so expensive that I can’t afford it), and I really have serious troubles with my HP 6715b (crappy laptop) and Linux distribution and also I can’t install Mac on him, mainly because its catastrophically poor choices in BIOS.

And so there’s only me and my buddy Windows 7 RC. Yep, on Ruby main site there is a One-Click installer but for version 1.8.6, but they have link on Ruby download page binary for 1.9.1-p0 which is old. No problem, you can go to Ruby FTP site and find latest Ruby 1.9.1-p129 binary.

The Problem and “FIX”!?

With this version I couldn’t even install RubyGems which is preliminary for installing Rails. What to do next, you guess it, ask my friend Google, and he is one of my best friends (in need). In whole bunch sites in search I found this one blog “The Cabin” with instructions for installing Ruby dependencies. After applying all of the dependencies I could install Gem and Rails with no problem. But, when I try to generate Rails application, guess it, I had an error, missing libiconv2.dll, and I found it, install it, life is good, everything worked.

RubyMine Idyll

I heard a lot of good words for the new JetBrains product RubyMine IDE for Ruby/Rails, since my must have tool is also JetBrains ReSharper (R#). It was a good reference to try it, and I can proudly say that RubyMine is great IDE. There was attempts for creating Ruby/Rails IDE but they are all very frustrating and disappointing. And also I have to mention the RubyMine price of 100$ is so affordable for all features given with it like refactoring, code completion, and support for my favorite Rails view engine Haml now only with syntax highlighting but with more to come.

So close, no matter how far

And everything is good, perfect I could say, but… I was getting to know RubyMine IDE, and after two and half years i start to create Rails application. Scaffolding was my primary choice, and it generate all the files for my entity “club” with few attributes like name, address, location… I was so excited, and all I have to do is run command rake db:migrate, and Rails shall create my SQLite (Since 2.2.1 default database for Rails) file with table “club” and all its given attributes.

The Frustration

I was so near, but I got error, the one reasonable. I haven’t installed Gem sqlite3-ruby, installing Gem, piece of cake, all I have to do is:

gem install sqlite3-ruby

In my command prompt there was some cryptic errors, hell with it, I don’t even need SQLite, MySQL is my database of choice, but I also should have to install mysql Gem

gem install mysql

can you guess, the cryptic error is all I got. Optimistically or naively I tried to execute rake db:migrate, and it fails, and for some unknown reason it prompt me with missing msvcrt-ruby18.dll. Why, I have msvcrt-ruby191.dll, why the hell it searches for old Ruby 1.8 dll.

The War

Now, I was really mad. The War is declared. I needed help, and I have to ask my old friend Google to find the answer to this mystery. I wasn’t the only one mad, and frustrated, many were defeated and get back to Ruby 1.8.6, but not me, I never give up, when there is hope – there is a will!

I stumble upon this Ruby forum post and found instructions for mysql-ruby in last but one reply. I did everything told, but with mysql-ruby-2.8 (in post he speaks of 2.7.3) version. I have made my own mysql-ruby Gem (I was happy) but when I tried to install it, same cryptic error.

The Light

I saw spark of light in this darkness when I stumble upon this post (and again on Google Group). Read the solution from Roger Pack, but at first I haven’t understand it, knew that I really missing MinGW but didn’t know what was really necessary to install to get it work properly.

And on the same post I found link to this blog post, and in it there is all I need to understand how to set up MinGW, and most importantly the link to the RubyInstaller downloads page.

RubyInstaller downloads page have two links the first is the binary of Ruby 1.9.1-p129 but not compiled as win32 (downloadable from main Ruby download page), but as mingw32 (both for i386 platform). To be more accurate about version:

ruby 1.9.1p129 (2009-05-12 revision 23412) [i386-mingw32]

And important one link to the Development Kit (MinGW 3.4.5 + MSYS 1.0.11), very much needed to complete the Robert Pack solution.

Note: Both links are 7zip archived files. It can be uncompressed with WinRAR.

Only I have to do is uncompress both files in C:\ruby directory, and have this directory structure:

• bin
• lib
• share
• devkit

To use Ruby easily in Command prompt, we have to put his bin directory in windows PATH. In Environment Variables for path add “C:\ruby\bin” and then open Command prompt and write this command:

C:\>ruby -v
ruby 1.9.1p129 (2009-05-12 revision 23412) [i386-mingw32]

The Solution

Now is coming Robert Pack solution in tasks assuming that step before is done (Ruby and MinGW in same directory):

1. Download MySQL 5.0 (not 5.1) for Windows “without installer (unzip in C:\)”.
2. Unzip, put it in c:\mysql
3. Download latest mysql-ruby source as version 2.8.1. Unzip it, and I put it in C:\ruby directory.
4. Edit extconf so that anywhere it says mswin32 it now says mswin32|mingw.
5. Make sure that mysql bin is *not* in your Windows PATH.
6. run in Command prompt ruby extconf.rb –with-mysql-include=c:/mysql/include –with-mysql-lib=c:/mysql/lib/opt.
7. run in Command prompt make, and then make install.
8. Add libmysql.dll to your windows PATH. The path is “c:\mysql\lib\opt”.
9. Exit from Command prompt, for PATHs to be reset, open again Command prompt and run ruby -e ‘require “mysql”‘

The Victory and the Final Touch

Now, everything should work, but we have two problems:

1. Give to the MySQL root user a password (default is blank password).
2. Set MySQL as windows service to start up automatically.

For giving root user a password run this command in Command prompt.

C:\>mysqladmin -u root password lida

Change “lida” to your favorite and top secret password.

For setting MySQL as Windows Service requires adding to Windows PATH a path to the bin directory of MySQL – “c:\mysql\bin”. In Command prompt then run:

C:\>mysqld-nt –install MySQL –defaults-file=”C:\mysql\my.ini”
Service successfully installed.

Note: The problem of downloaded MySQL is that have many my-*.ini files. And there is no my.ini file. So above command couldn’t be executed, only if you renamed one. Here is mine my.ini:

Copy this and create my.ini file and paste it in MySQL directory. Be sure that MySQL is in C:\mysql directory!

Now in Windows Service should be new service with name MySQL.

Note: If something goes wrong and you are stuck with not properly working service. Remove it with this command in Command prompt:

C:\>mysqld –remove
Service successfully removed.

The Beauty in Red (as Ruby)

Now is time for beauty, after all the pain and frustration. We have to test all the glory of the Ruby and it web framework Rails. You can follow me, and Command prompt is your only friend now. Save the bliss of RubyMine for later time!

C:\>cd ruby

C:\ruby>mkdir railsapps

C:\ruby>cd railsapps

C:\ruby\railsapps>rails testapp

You’ll get listing of all files generated, but we have to set up our new application to find our MySQL database. The directory that we are interested in is in your new application directory (C:\ruby\railsapps\testapp). Here you will find config directory, and inside database.yml YAML file. It is now configured for SQLite3 but we have to adjust it to MySQL, and here it is.

Of course, change password to suite your needs!

Note: That I have one database for development, test and production. This is not wise but for the test application it is OK!

First make sure that you are in your application directory. Then we shall do some scaffolding like this:

C:\ruby\railsapps>cd testapp

C:\ruby\railsapps\testapp>ruby script\generate scaffold Club name:string adress:
string location:string contact_person:string phone:string

You should see yet again whole bunch of generated files. Now, the great finale, migrate our model to the MySQL database with help of rake:

C:\ruby\railsapps\testapp>rake db:migrate
(in C:/ruby/railsapps/testapp)
== CreateClubs: migrating ==================================================
– create_table(:clubs)
-> 0.0910s
== CreateClubs: migrated (0.0920s) ===========================================

Only thing is now is left, to start server and test our new web application:

C:\ruby\railsapps\testapp>ruby script\server
=> Booting WEBrick
=> Rails 2.3.2 application starting on http://0.0.0.0:3000
=> Call with -d to detach
=> Ctrl-C to shutdown server
[2009-06-18 13:34:48] INFO WEBrick 1.3.1
[2009-06-18 13:34:48] INFO ruby 1.9.1 (2009-05-12) [i386-mingw32]
[2009-06-18 13:34:48] INFO WEBrick::HTTPServer#start: pid=4836 port=3000

Note: In starting of server I got error again missing msvcrt-ruby18.dll, but on click OK server is started and everything works perfectly.

Open your favorite browser and write or copy or run it from here http://localhost:3000/Clubs

And now we could add new club:

And we have first club:

In listing we have one club.

And that’s all folks. I think is worth trying Ruby and Rails even though they are not good and flexible in Windows as in Linux and Mac. Next post will be for setting Git source code management for source control and for downloading many Rails plugins.

Update: The complete working Ruby 1.9.1 directory with MinGW Development Kit, Rails and mysql-ruby 2.8.1 you can download it from ruby.rar.