Simple Postgresql Cron Backup

Here is a very simple method to backup your postgresql DB using a cron job and the pg_dump command. The main problem people encounter when trying to automate the pg_dump command is the password input. Now you could easily create a local user with no password, but why would you want to do that.

Read the rest of this entry »

Installing MySQL gem for Ruby 1.9.x

As of this writing, the MYSQL gem cannot be installed by simply doing a GEM INSTALL with ruby 1.9.1. In order to install the gem, follow these easy steps.

First download the mysql gem from rubyforge, and unzip it:

sudo wget http://rubyforge.org/frs/download.php/51087/mysql-ruby-2.8.1.tar.gz

 sudo tar -xzvf mysql-ruby-2.8.1.tar.gz

 cd mysql-ruby-2.8.1

Read the rest of this entry »

ODBC.ini update did not work

After running the same database for quite some time for a rails application, we finally had to change the database in odbc.ini to point to the new server. Alas we changed the odbc.ini file, shut down the old database and relaunched the application. Expecting the application to immediately start talking to the database on reboot (since we just changed an IP) we were surprised to find that the application was not connecting to the database anymore.

The rails application was connecting to SQL Server as detailed in this previous post using ODBC and FreeTDS.
Read the rest of this entry »

Finding Duplicate Fields in a Database Table

Occasionally it is necessary to find duplicate fields within a database table.  The following query can be used to accomplish this easily.
SELECT *
FROM 'table'
WHERE ('item' IN (SELECT 'item'
FROM 'table'
GROUP BY 'item'
HAVING COUNT(*) > 1))
ORDER BY 'item'

This will list all rows in the desired ‘table’ where the given field (‘item’) is duplicated. This is a great query to use if you want to find duplicate email addresses in a database, or any other field that may be duplicated.

Read the rest of this entry »

Quickly Restore a SQL Server Database

If you frequently need to restore other peoples DB’s for testing here is a quick way to do so in Microsoft Sql Server.

1. Open your Sql Server manager
2. Create a new database (note the names and locations of the created files)
3. Select the newly created DB and select Restore.
4. Select from Device and choose the backup file to restore.
5. Make sure you are overwriting the new database you created, usually quite apparent, look at the path.
6. Check the log file and database name, modify where needed to the new database and log files (usually under logical name)
7. Hopefully once set, you can restore the database, proceed hopefully, if not check the error and modify accordingly.
8. Now you likely need to take ownership of the DB, run the following on the newly restored database to give it sa ownership:

   exec sp_changedbowner 'sa','true'

Read the rest of this entry »