How to create a copy of a table using a background script - Support and Troubleshooting

How to create a copy of a table using a background script Issue .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } This article explains how to create a copy of an existing table so that you can access the copied table from the UI or from a background script. You may need to copy a table for a test case or to back up data from a rotated shard before it gets truncated. The new table is empty and does not contain data. You must copy date from the source table as a separate process.

Although ServiceNow can create a backup of a table from the backend, you cannot access it from the instance such as from a form, list, or background script (/sys.scripts.do). The application does not recognize the new table because the application requires associated records in the metadata tables (sys_db_object, sys_dictionary, sys_documentation, and others).

Important considerations: The script in this article does not work for CMDB tables.

This script uses a deprecated script include for Table Rotation to construct the new table. The resulting table may have differences, such as adding the edge_encryption_enabled attribute on the new table's sys_dictionary records. This is not an exact copy. The edge encryption attribute only has impact if Edge Encryption is licensed, set up, and configured. Edge Encryption is deprecated and can no longer be purchased. For more details on Edge Encryption, see the ServiceNow documentation .

Release .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } All supported releases

Resolution .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Before New York release 1. Go to /sys.scripts.do.

2. Copy the following code and customize it for your environment.

The script accepts three arguments:

First argument : The source table to be copied Second argument : The new target table name Third argument : Boolean value to control index creation. Set the value to false if you do not need to create indexes. (Creation time is fast either way because the table is empty.) cpTable('sys_upgrade_history_log', 'u_testcase', true); function cpTable(strOldTable, strNewTable, bCopyIndexes) { var tu = new TableUtils(strNewTable); var bNewTableAlreadyExists = tu.tableExists(); if (bNewTableAlreadyExists) { gs.print("WARNING: Target Table " + strNewTable + " already exists! Please choose a new target table name"); } else { var gr = new GlideRecord(strOldTable); gr.initialize(); var td = GlideTableDescriptor.get(strOldTable); var tdNewTable = new TableDescriptor(strNewTable, gr.getLabel()); var dbo = new GlideRecord("sys_db_object"); dbo.addEncodedQuery("super_classISNOTEMPTY^name=" + strOldTable); dbo.setLimit(1); dbo.query(); if (dbo.next()) { tdNewTable.setExtends(dbo.super_class + ''); } tdNewTable.setFields(gr); tdNewTable.copyAttributes(td); tdNewTable.setRoles(td); tdNewTable.create(); if (bCopyIndexes) { tdNewTable.copyIndexes(strOldTable, strNewTable); } } }

3. Select Run Script .

The script creates a new table called u_testcase . You can access this table at /u_testcase_list.do or using GlideRecord('u_testcase'); .

Example output:

[0:00:01.621] Script completed in scope global: script Creating table: u_testcase TableCreate for: u_testcase DBTable.create() for: u_testcase Replication is not enabled on table: u_testcase, not queueing replication table create special db event *** Script: Begin ResourceSupport.buildTableResources(u_testcase, undefined) *** Script: End ResourceSupport.buildTableResources LicensingTableCreateListener: Initializing licensing attrs for table u_testcase Time: 0:00:00.615 id: tundra_1[glide.2] for: SELECT sys_storage_alias0.`table_name`, sys_storage_alias0.`element_name`, sys_storage_alias0.`storage_alias` FROM sys_storage_alias sys_storage_alias0 WHERE sys_storage_alias0.`storage_alias` != sys_storage_alias0.`element_name` /* tundra004, gs:329947A4DBAE4700671C51035E9619B8, tx:52c94328dbae4700671c51035e9619cb */ Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`sys_source_table`) ,ADD INDEX (`update_set`) ,ADD INDEX (`upgrade_history`) Duplicate index, skipping: u_testcase([sys_source_table]) NONUNIQUE Duplicate index, skipping: u_testcase([update_set]) NONUNIQUE Redundant index check on u_testcase found redundant index upgrade_history (upgrade_history) NONUNIQUE; caused by new index (upgrade_history,file_name,sys_recorded_at) NONUNIQUE Redundant index check on u_testcase found 1 redundant indexes Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`upgrade_history`, `file_name`, `sys_recorded_at`) ,ADD INDEX (`upgrade_history`, `order`) ,ADD INDEX (`upgrade_history`, `disposition`, `resolution_status`, `changed`, `order`) ,ADD INDEX (`upgrade_history`, `resolution_status`, `disposition`, `type_priority`) ,ADD INDEX (`upgrade_history`, `disposition`, `changed`) ,ADD INDEX (`order`) Dropping index(es): ALTER TABLE `u_testcase` DROP INDEX `upgrade_history` If you run the same script again with the same target table name, the following warning appears:

[0:00:00.000] Script completed in scope global: script *** Script: WARNING: Target Table u_testcase already exists! Please choose a new target table name New York and subsequent releases 1. Go to /sys.scripts.do .

2. Copy the following code and customize it for your environment.

The script accepts three arguments:

First argument : The source table to be copied Second argument : The new target table name Third argument : Boolean value to control index creation. Set to false if you do not need to create indexes. (Creation time is fast either way because the table is empty.) cpTable('sys_upgrade_history_log', 'u_testcase', true); function cpTable(strOldTable, strNewTable, bCopyIndexes) { var tu = new TableUtils(strNewTable); var bNewTableAlreadyExists = tu.tableExists(); if (bNewTableAlreadyExists) { gs.print("WARNING: Target Table " + strNewTable + " already exists! Please choose a new target table name"); } else { var gr = new GlideRecord(strOldTable); gr.initialize(); var td = GlideTableDescriptor.get(strOldTable); var tdNewTable = new SNC.TableRotationBootstrap(strNewTable, gr.getLabel()); var dbo = new GlideRecord("sys_db_object"); dbo.addEncodedQuery("super_classISNOTEMPTY^name=" + strOldTable); dbo.setLimit(1); dbo.query(); if (dbo.next()) { tdNewTable.setExtends(dbo.super_class.name + ''); } tdNewTable.setFields(gr); tdNewTable.copyAttributes(td); tdNewTable.create(); if (bCopyIndexes) { tdNewTable.copyIndexes(strOldTable, strNewTable); } } }

3. Select Run Script .

The script creates a new table called u_testcase . You can access this table at /u_testcase_list.do or using GlideRecord('u_testcase'); .

Example output:

Creating table: u_testcase TableCreate for: u_testcase DBTable.create() for: u_testcase Replication is not enabled on table: u_testcase, not queueing replication table create special db event *** Script: Begin ResourceSupport.buildTableResources(u_testcase, undefined) *** Script: End ResourceSupport.buildTableResources LicensingTableCreateListener: Initializing licensing attrs for table u_testcase Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`sys_source_table`) ,ADD INDEX (`upgrade_history`) ,ADD INDEX (`update_set`) [0:00:13.170] DBTable.create of: u_testcase [0:00:14.839] Table create for: u_testcase Duplicate index, skipping: u_testcase([sys_source_table]) NONUNIQUE Duplicate index, skipping: u_testcase([update_set]) NONUNIQUE Redundant index check on u_testcase found redundant index upgrade_history (upgrade_history) NONUNIQUE; caused by new index (upgrade_history,order) NONUNIQUE Redundant index check on u_testcase found 1 redundant indexes Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`upgrade_history`, `order`) ,ADD INDEX (`upgrade_history`, `resolution_status`, `disposition`, `type_priority`) ,ADD INDEX (`upgrade_history`, `file_name`, `sys_recorded_at`) ,ADD INDEX (`upgrade_history`, `disposition`, `changed`) ,ADD INDEX (`upgrade_history`, `disposition`, `resolution_status`, `changed`, `order`) ,ADD INDEX (`order`) Dropping index(es): ALTER TABLE `u_testcase` DROP INDEX `upgrade_history` Altering storage table [sh$sys_cache_flush]: ALTER TABLE sh$sys_cache_flush ADD `sh$context` VARCHAR(32) , ADD `sh$operation` VARCHAR(40) , ADD `sh$change_count` INTEGER , ADD `sh$first_recorded` DATETIME , ADD `sh$last_recorded` DATETIME , ADD `sh$sequence` VARCHAR(40) , ADD `sh$first_txn_id` VARCHAR(32) , ADD INDEX `mnixgjyj_source_primary`(`sys_id`) *** Script: Begin ResourceSupport.buildTableResources(sh$sys_cache_flush, undefined) *** Script: End ResourceSupport.buildTableResources