backups and timestamps in table names


backups and timestamps in table names


I run a number of simple programs in SQL Assistant that insert rows into tables for predictive modeling (I'm a user, not a DBA).  Before each insert I make a copy of the target table as a backup.   The backup table's name is the target table's name with a "_bu" suffix.

The good side is that I can run these jobs, have a backup, and not have to type in a table name each time for the backup.   The bad side is that I only have one backup.

I was considering adding a timestamp column to the backup table and be able to insert multiple copies of the target table.  Then, I could delete certain backups periodically.  Alternatively, is there a way to copy a table with a name that includes a timestamp (tableA_<timestamp1>, tableA_<timestamp2>, tableA_<timestamp3>)?  Are there better approaches?  For instance, would  you suggest a script (BTEQ)?

Thank you

Tags (3)
Junior Contributor

Re: backups and timestamps in table names

Dynamically creating a tablename is not possible without some kind of Dynamic SQL, you need a Stored Procedure for this and your DBA probably didn't grant you this right.

A BTEQ script will need an EXPORT plus RUN FILE or shell scripting and it's an extra step when you use SQL Assistant.

So i would prefer having multiple old versions in a single table.



Re: backups and timestamps in table names

Thank you Dieter.  Very helpful.