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
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
--Remove dashes on the line below to execute
--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.
Quick SQL Table Restore
Identify SQL Table Backups