Clone db for testing

Scriptcase 9 General
#1

I am in system test mode

I would like to take a snap shot of my db at a time of my choosing, so I can go back to that point if my tests mess up.
Is there a simple sql command that will clone a table, with data, to a new table?
Is there an easy way to b/u the db with code, and then restore simply?

what are best practices out there for doing this?

L

1 Like
#2

I already answer you about this

1 Like
#3

Tks

So I created a table with 1 ROW for each of my db table names {tbl}
set $xx = “whateverPrefixString”.{tbl}"

Then created an app with a run button with this code:

sc_exec_sql(“DROP TABLE IF EXISTS $xx”;);
sc_exec_sql(“CREATE TABLE $xx LIKE {tbl}”;);
sc_exec_sql(“INSERT INTO $xx SELECT * FROM {tbl}”;);

Then created another app that copies it back

Tks

1 Like
#4

Get a program like Navicat
Makes it easy to backup and restore a database.

#5

Hi
in my case i wanted give sync functionality to the end users, they want to sync production , test or any other data with each other.
you basically need to create different schema’s for each environment. i use Azure SQL.
you can script your tables and then create them in another schema. All sync is done on the database side using a stored procedure. this procedure is on my github repository.

i have a form app to sync tables . Table names are stored in {tables} field, coma separated.

there is a php button that runs this php

$schema_from=’’;
$schema_to=’’;
$m=’’;

echo ‘START SYNC AT ‘. date(“Y-m-d H:i:s”).’
’;

// Check for record
$check_sql = “SELECT connection_schema from sec_connections where connection_guid=’”.{connection_from_guid}."’";

sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
$schema_from={rs[0][0]};
}

$check_sql = “SELECT connection_schema from sec_connections where connection_guid=’”.{connection_to_guid}."’";

sc_lookup(rs, $check_sql);

if (isset({rs[0][0]})) // Row found
{
$schema_to={rs[0][0]};
}
$m=‘sync tables from schema ‘.$schema_from.’ to schema ‘.$schema_to.’
’;

$table=explode(",", {tables});

foreach ($table as $t) {
if($t>’’ && $schema_from>’’ && $schema_to>’’ && $schema_from<>$schema_to){
$m=$m.‘sync table ‘.$t.’ AT ‘. date(“Y-m-d H:i:s”).’
’;

	$str_sp = "EXEC A_SP_SYS_LOG @site_id='".[current_site]."', @object_id='".$t."', @category='".'START COPY DATA'."'";
	sc_lookup(rs,$str_sp);

	$str_sp = "EXEC MSQL_SP_TABLE_COPY @SCHEMA_FROM='$schema_from', @SCHEMA_TO='$schema_to',@TABLE_FROM='$t'";
	sc_lookup(rs,$str_sp);
	if(isset({rs[0][0]})){$m=$m.{rs[0][0]}.'<br><br>';} 
}

}

$m=str_replace("’","’’",$m);

// SQL statement parameters
$update_table = ‘dbo.sec_tables_sync’; // Table name
$update_where = “id = '”.{id}."’"; // Where clause
$update_fields = array( // Field list, add as many as needed
“messages = ‘$m’”,
);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

echo 'FINISHED SYNC AT '. date("Y-m-d H:i:s");
1 Like
#6

Very expensive for just that

#7

@lewis200 you can try DBeaver Community Edition. I’m using it with no problems to create clone db for testing.

#8

Oh it does a LOT more than that. Automatic backups…you name it. The query builder is also excellent.

#9

I meant if that’s all I needed it for, it would be too expensive

#10

You don’t have to create a table for that, you can get the list of your table with

SELECT table_name FROM information_schema.tables WHERE table_schema = ‘database_name’ AND table_name NOT LIKE ‘wateverPrefixString%’;

1 Like
#11

Have been looking for a way to do that for a long time… thanks so much!

1 Like
#12

Good luck, it is pretty amazing that database do not let you simply make a copy of a table :slight_smile: similar to files but require all this heavy lifting.
we come from a really weird world.

#13

I agree, I always thought there needed to be a simpler way

#14

If every function already existed, there will be no need for programmer.

Even a simple function like copying a table comes from a library on top of a lower level of the OS

There are some library available for SQL that will do those function, like copying a table or a database

1 Like
#15

If you use SQLITE Database, You don’t have any problem for copy your database file wherever.

1 Like