Tuesday, August 26, 2008

Assign a variable from a Select result with SQL Server

Sometimes will be useful saving a Select query result in a variable, perhaps to reuse it inside another query.
The command to do that is very simple:

DECLARE @variable AS datatype

SELECT @variable = Filed FROM Table WHERE Condition

We only have to comply with certain conditions:
  • datatype must be of the same datatype of Field
  • if the query return more than 1 row, the variable contains the last row value
  • is not possible to use TOP clause

Update from Select with SQLServer

Suppose to be in this situation: we have to update some database records starting from query result. There are 2 solutions: we can manual update datas (it will be a problem with thousands of records) or we can use a T-Sql script to do exatcly what we want. Then only condition is that the starting query must have a field on wich we can make a join on.
The script code is below:

UPDATE T
    SET T.Field1a = Q.Field2a,
    T.Fieldb = Q.Field2b
FROM
    (SELECT FieldID, Field2a, Field2b
    FROM Table WHERE Condition) Q
INNER JOIN TableToUpdate T
    ON T.FieldID = Q.FieldID


Where T is the "Table-To-Update" alias and Q is the Select query alias.
The Join clause do the update exactly like we do an "Update ... Where".

Thursday, August 14, 2008

TrixBox Backup

Mondoarchive is a backup solution for *nix systems. It creates .iso files with cd or dvd sizes containing the entire image of disk (like ghost for Windows). It's an extremely powerful solution 'cause, when burned, images are bootable and autorestoring. In this way it's possible to recover the system installation without the reinstallation of all sofware and drivers, for example in case of disk crash.
Moreover, the startup/config script allow to choose where to create image files: on disk, on usb devices, on samba/nfs shares or on ftp.
For any other information, please visit developers site: http://www.mondorescue.org/
The procedure below is optimized for backupping Trixbox versions 2.4 and 2.6 and Asterisk-based systems.

Installation
As root user, do:
cd ~
wget http://www.dbtek.it/Repository/install-mondo.tgz
(or from http://www.astusers.org/install-mondo.tgz)
tar -xzvf install-mondo.tgz
chmod +x install-mondo.sh
./install-mondo.sh

This script downloads all dependencies, install mondo backup program and create a custom configuration for trixbox.
At the installation end, it's necessary to reboot the system.

Next we have to edit the /etc/cron.weekly/mondobackup.cron file to activate the desired backup options and to configure the program. Every instruction needed are written directly into this config file.

To test if the procedure is working as we want: /etc/cron.weekly/mondobackup.cron

WARNING: if there are errors on mounting samba file systems (Netowrk Sharing on Microsoft OS) and yours username or password used for accessing shares contains symbols you must write a ' before and after it.


Restore a backup
To restore a backup we can choose from diffrent ways:

Nuke (formats the destination disk and automatically restores all)
Interactive
Expert

It's also possible to try to restore only the bootloader (if the disk didn't crash):
• Boot from backup CD/DVD and choose Expert Mode
• Type: mondorestore --mbr
• Choose option 28 and then
bash# mount-me
bash# chroot /mnt/RESTORING
bash# lilo or grub-install ’(hd0)’
bash# exit
bash# unmount-me

Nuke Restore
As said, this option completely overwrites the system hard disk, automatically restoring the backup

• Boot from first backsup CD/DVD
• Choose RESTORE
• When asked, insert next CD/DVD (if exists)
• Check for errors

If there are errors, check /tmp/mondorestore.log file.

If you wanna see what the program is doing, press + to analyse the log file “on-the-fly”.

Interactive Restore
The Interactive Mode allow to restore a subset of files from backup or we can use it to restore all files without formatting hard drive.

The interactive restore give the “Editing mountlist screen” that allow to set diffrent sidk geometries. To move through the partitions use up and down arrow keys. To move on buttons use left and right arrow keys.

To restore a subset of files, the procedure is:
• Boot from first backup CD/DVD
• Type interactive
• Answer to questions:
Do you want to partition your devices? no
Do you want to format them? no
Do you want to restore everything? no
Do you want to restore something? yes
Which path do you want to restore? /mydata (for example)
Do you want to run LILO to setup your boot sectors? Yes

Expert Restore
To manually restore:
• Boot from first backup CD/DVD
• Type expert
• Type mondorestore