Deleting all tables from the database...

after almost 2 hours of clogging my head and thinking about how to dump the data from the local forum that I created for the sura platform to the remote database in CS server, finally managed to create a page that will help me with this kind of situation further...

/* fill in your database name */
$database_name = "my_db";

/* connect to MySQL */
if (!$link = mysql_connect("db_host_name", "username", "pass")) {
die("Could not connect: " . mysql_error());
}

/* query all tables */
$sql = "SHOW TABLES FROM $database_name";
if($result = mysql_query($sql)){
/* add table name to array */
while($row = mysql_fetch_row($result)){
$found_tables[]=$row[0];
}
}
else{
die("Error, could not list tables. MySQL Error: " . mysql_error());
}

/* loop through and drop each table */
foreach($found_tables as $table_name){
$sql = "DROP TABLE $database_name.$table_name";
if($result = mysql_query($sql)){
echo "Success - table $table_name deleted.";
}
else{
echo "Error deleting $table_name. MySQL Error: " . mysql_error() . "";
}
}

Thanks to http://www.ebrueggeman.com/blog/drop-all-tables-in-mysql/

In the local machine, the GUI can generate the sql script (along with insert commands), or from the command line can also be done as:

mysqldump -u username -ppassword --databases databasename > database_script.sql

(if no database is specified, all the databases will be dumped/copied)

Then, to restore database from backup.

 mysql -u username -ppassword databasename < database_script.sql 

And thats all, the database from my local machine got transferred to the CS SQL server.