Tuesday, January 17, 2012

Making a backup from the database or only a backup from the data

In my environments I have been asked to make a backup from a database in the test environment because the developer wanted to test a script that they have made. This script deletes,  updates and/or inserts rows in one or more table. If it only concerns one or more tables with only a few records inside. Then I usual advised the to make a backup from the data inside their own database.

SELECT *
     INTO test_table_save
  FROM test_table

This makes a copy of the table test_table into the table test_table_save. It doesn't copy any trigger, constraints or indexes on that table.

If the script has run you can use the following command to get back to the original state.

TRUNCATE TABLE test_table

INSERT INTO test_table
  SELECT *
      FROM test_table_save


This makes the developer independent from a DBA and it gives him/her more flexible.

No comments:

Post a Comment