RollBack a Dropdatabase?

jedimastermopar · 05-26-2008, 10:40 AM

I was using testing a piece of software on a database that has not been backed up. I know I know stupid me. Anyway I was using it to insert a new table, and I apparently checked a button to drop the database if it already existed and the table got dropped. grrr, not a huge deal but I would really like to be able to recover the tables.
Can a Drop Database be rolled back without the use of backups?

tredegar · 05-26-2008, 11:47 AM

I think it depends on your SLQ engine, and how it was set up.
For example:

Quote:

http://dev.mysql.com/doc/refman/5.0/en/commit.html
"By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.
.
[SNIP]
.
To disable autocommit mode for a single series of statements, use the START TRANSACTION statement:

Code:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state"

chrism01 · 05-26-2008, 06:59 PM

As a general rule in RDBMSes (ime), DML (data manipulation lang eg SELECT/INSERT/UPDATE/DELETE) can be rolled back IFF you are using txns (as above).
HOWEVER, DDL (data definition lang eg DROP/CREATE etc) usually can't, even with txns.
Check the docs for your DB.