PradysTechBlog

Yet another blog

Category Archives: Database

How to import table using “INSERT” statement?

You can use the following SQL statement

INSERT INTO table1 SELECT * FROM Table2

Make sure that the structure of the select statement is same as the structure of table1

What is Cascade and Restrict when we use DROP table in SQL SERVER

A table can have object dependencies. Indexes, views, programs.

When you give a “drop table” command, it will not drop the table that has dependencies unless you include the “cascade” command in the statement. The database programming is written this way to make sure you know the table has dependent objects and makes you explicitly say you want to drop the table and all its dependencies.

CASCADE has the effect of dropping all SQL objects that are dependent on that object. RESTRICT is the default for the drop behavior. RESTRICT looks to see what objects are dependent on the object being dropped. If there are dependent objects, then the dropping of the object does not occur.

How to backup and restore a huge DB in MySQL (timeout prob in phpmyadmin)

PHPmyadmin is the most widely used tool administer a MySQL database. But the major problem with this web based tool is its inablity to load big Databases. How many times we have had to import a big database and get timed out. On the look out for an alternative to PHPmyadmin,i tried using MySQL GUI tools. Its been nearly 6 months i have been using this to backup and restore my MySQL databases.

You can download the  MySQL GUI from http://dev.mysql.com/downloads/gui-tools/5.0.html 

Install the application and you can find the gui tools under start->Programs ->MySQL

You can use this software to administer the DB, Query the DB and also for migrating the DB.

We will discuss in this article on how to take a Backup and restore and Database of MYSQL using this MySQL gui tools.

Go to start->programs->MySQL->MySQLAdministrator

MySQL Administrator LoginBy Clicking  on the button next to StoredConnection  You can store details of multiple connections so you dont have to remember the username and server details. This is particularly useful if you are handling multiple Databases. You give a name in Connection and it saves the login details so next time you want to access the DB you can choose the the name from the storedConnection.

HostName:  This is your DB Ipaddress or servername, if you are using local machine, you can give it as localhost.

Username : Enter the database username

Password : Enter the password of database.

Schema : Enter the Database name

click on apply and you are ready to login to the DB

Click on OK button.

Backup This is the screen you get after clicking the OK button.

This is where we can take backup and restore a MySQL DB.  For taking a backup just click on Backup  and then click on new project. Enter a name for the project and you can see that the Databases which are available for the username which we logged in is listed under schemata.

Select the database you want to take a backup and click on the right arrow to load the tables. This may take a few seconds or run upto a minute depending upon your internet connection and size o database. If you are using localhost this would load in few seconds.

By default all the tables are selected, you can choose to unselect individual tables or all the tables by clicking on the tick mark on the left of the database name in the backup content window or by unselecting the tick mark before the individual tables.

One can further configure the backup by going to advanced option tab.

Click on execute Backup button to start your backup process. This will get an file dialog box. Choose where you want to save the file. It would save the file with the name of the project and datetime appended to it. This again could take a few minutes depending on the database size and the connection speed.

Now you have the backup ready in your hard drive. We will discuss in the next article on how to restore the database.

Please write in your comments and suggestions.