Quick SQL Table Backup

Occasionally one needs to perform an action in either EnterpriseOne or SQL Server that places the data in one or more tables at risk. Examples include an index or (obviously) a table generation in OMW, a direct update to the data using a query, the first run of a custom UBE that updates data, etc. Best practices dictate that you should get a backup of the data.

Rather than using the time consuming process of backing up the entire database, and in the absence of table-specific backup/restore tools, one can quickly and easily make a copy of the table using standard SQL T-SQL code.

The command is SELECT INTO, or actually a combination of the SELECT command and the INTO clause. The basic statement looks something like


SELECT *
INTO new_table_name
FROM old_tablename

or

SELECT *
INTO JDE_PRODUCTION.PRODDTA.F0101_BAK
FROM JDE_PRODUCTION.PRODDTA.F0101


While this simple piece of code is sufficient to perform the copy, several problems arise. First, the code above requires you to correctly specify both the source and target table. Also, the SELECT INTO method will create a new table but it will not overwrite a table with the existing name. So, unless you feel like checking for the existence of a table named "JDE_PRODUCTION.PRODDTA.F0101_BAK" every time you perform this action I would suggest using the code below.

In this version we get a uniquely named table as long as you don't run it twice within a minute. The name will consist of the original table name plus characters representing the year, month, day, hour and minute. To do this, we grab the current date and time using GETDATE, convert it to text, replace characters like spaces, commas, etc. that we do not want in the table name, build this into a string, then use EXEC (@SQL) to run the query.

The code looks like this:

--SQL Script begin
DECLARE @Tablename NVARCHAR(500)
DECLARE @BuildStr NVARCHAR(500)
DECLARE @SQL NVARCHAR(500)
SET @Tablename = 'JDE_PRODUCTION.PRODDTA.F0101'
SET @BuildStr = CONVERT(NVARCHAR(16),GETDATE(),120)
SET @BuildStr = REPLACE(REPLACE(REPLACE(REPLACE(@BuildStr,'
',''),':',''),'-',''),' ','')
SET @SQL = 'select * into '+@Tablename+'_'+@BuildStr+' from '+@Tablename
SELECT @SQL
--Remove dashes on the line below to execute
--EXEC (@SQL)
--SQL Script end

and generates a query that looks like this:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F01012


In the example above we have specified the three part name "JDE_PRODUCTION.PRODDTA.F0101" in the @Tablename variable. You would change this variable to specify your table. Figured I'd mention that 'cause you just never know.

As constructed above, the query runs SELECT @SQL and will simply return the query string we built. It will not execute the copy. Once you are satisfied that the query string built is correct, remove the dashes from in front of EXEC (@SQL) to run the table copy.

You will see something like this when properly executed:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F0101

(1 row(s) affected)

(65436 row(s) affected)


Once you have completed the quick table copy you can now continue with the action that placed the data in danger knowing that you have a copy of the data.

Some caveats apply: the copy only brought over the data. No indexes exist on the backup copy of the table. Also, you now have an easily forgotten extra copy of what might be a very large table. Clean it up as a part of your scheduled maintenance, set a reminder to delete it later or use this method to automatically identify SQL Table Backups for removal.


Related postings:

Quick SQL Table Restore
http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html