Migrate Data from one instance to another

restevao
Giga Expert

on ‎03-20-2022 04:35 AM

When migrating data between instances you have few options.

1) export CSV/Excel and import via import set, (this can also be enhanced with a transform map)

2) exporting an XML file and importing into the target instance

3) adding records to an update set via script. Then moving that update set via retrieval mechanism on the target instance.

var gra = new GlideRecord("XXXXXXX");
gra.addEncodedQuery("XXXXXX");
//gra.setLimit(10);
gra.query();
//gs.log(gra.getRowCount());

var updateManager = new GlideUpdateManager2();
while(gra.next())
{
    updateManager.saveRecord(gra);
}

4) rest API calls

Each have benefits and are most appropriate to use at certain times.
The difficulty presents its self when you want to moving large amounts of data as an exact replica of itself or even with slight transforms

One thing that is for sure, time is required to process the items

 

I have written a particular script (below) that i find can be useful when migrating large amounts of data between SNOW instances.

what is particularly nice about this script is that it contains a function that can be used to make an exact copy of any record between the source and target SNOW instances. see below

var ThrottleLimit = 50; //2 sec

function pausecomp(ms)
{
	ms += new Date().getTime();
	while (new Date() < ms)
	{}
}


//manage offsets and limits to ensure no corruption of calls with rest api
for (var count = 0 ; count <= 300 ; count++)
{
	caller(100,100*count);
	pausecomp(500);
}



function caller(limit,offset)
{
	var restEndpoint = "https://XXXXX.service-now.com/api/now/table/incident?sysparm_query=&sysparm_display_value=false&sysparm_exclude_reference_link=true&sysparm_suppress_pagination_header=true&sysparm_limit=" + limit + "&sysparm_offset=" + offset;

	puller(restEndpoint);	
}

function puller(restEndpoint)
{
	// neets update to transform table to
	var oldTableName = restEndpoint.split("?")[0].split("/")[6];
	var instanceRestName = restEndpoint.split("?")[0].split("/")[2].split(".")[0];

	
	var r = new sn_ws.RESTMessageV2();
	r.setEndpoint(restEndpoint);
	r.setHttpMethod('GET');
	r.setRequestHeader('Content-Type', 'application/json');
	r.setAuthenticationProfile('basic', "c8ac221adb1209900034150505961988");  //sys_auth_profile_basic.do?sys_id=c8ac221adb1209900034150505961988  //!!!! DATE TIME OF AUTH USER MUST BEEN SET TO GMT
// refers to the account on the target instance 			

	try
	{
		var response = r.execute();	
	}
	catch(e)
	{
		gs.log("ERROR: Sending REST API " + e + "\n\n" + restEndpoint, "DATA_MIGRATION_LOG");
	}

	if (!response.haveError())
	{
		var parsed = JSON.parse(response.getBody());
		var results = parsed.result;

		//for (var x = 0; x < results.length ; x++ ) // loop over json results

		results.forEach(function(item) //loops all fields in results
		{
			//SET TABLE
			var gr = new GlideRecord(oldTableName);
			gr.initialize();

			for (var key in item)  //Default sets all the fields that exist in the table. Custom fields that dont match are SKIPED
			{	
				if(key == "sys_id") gr.setNewGuidValue(item.sys_id);
				if(key != "sys_tags" && key != "sys_class_name" )gr.setValue(key, item[key]); //if(key == "sys_created_on" || key == "sys_updated_on")//spent tooooo long on this. 
			}

			//TRANSFORM DATA HERE

			gr.setValue("short_description" , "MIGRATED_" + item.short_description);
			//gr.sys_scope = "";
			//gr.sys_package = "";


			gr.autoSysFields(false);
			gr.setWorkflow(false);
			var recordSys_id = gr.insert(); 
		
			if (recordSys_id)
			{
				pausecomp(ThrottleLimit); //Throttle Limit - wait
				// voice call logs
				getRestQuery(instanceRestName,"sys_journal_field?&sysparm_query=element_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_history_set?&sysparm_query=id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_history_line?&sysparm_query=set.id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_audit?&sysparm_query=documentkey%3D" + recordSys_id,"","0");
				//pausecomp(ThrottleLimit);
				getRestQuery(instanceRestName,"question_answer?&sysparm_query=table_sys_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sc_multi_row_question_answer?&sysparm_query=parent_id%3D" + recordSys_id,"","0");
				//pausecomp(ThrottleLimit);
				getRestQuery(instanceRestName,"sys_attachment?&sysparm_query=table_sys_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_attachment_doc?&sysparm_query=sys_attachment.table_sys_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_email?&sysparm_query=instance%3D" + recordSys_id,"","0");
				//pausecomp(ThrottleLimit);
				//DISABLE SLA in PROD - can be reclac //
				//getRestQuery(instanceRestName,"task_sla?&sysparm_query=task%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sysapproval_approver?&sysparm_query=sysapproval%3D" + recordSys_id,"","0");	
			}
			else
			{
				gs.log("NOT INSERTED: " + oldTableName + "." + item.sys_id + " : " + gr.getLastErrorMessage(), "DATA_MIGRATION_LOG");
			}
		});

	}
	else
	{
		var responseBody = response.getBody();
		var message = responseBody + "\nStatus Code: " + response.getStatusCode() + "\nError Code: " + response.getErrorCode() + "\nError Message: " + response.getErrorMessage();
		gs.log("ERROR: responce REST API " + restEndpoint + "\n\n" + message, "DATA_MIGRATION_LOG");
	}
}


function getRestQuery(instanceName,tableQueryStr,returnlimit,queryoffset)
{
	var instanceAPI = "https://" + instanceName + ".service-now.com/api/now/table/";
	var reqParams = "&sysparm_display_value=false&sysparm_suppress_pagination_header=true&sysparm_exclude_reference_link=true";
	var limit = "&sysparm_limit=" + returnlimit;
	var offSet = "&sysparm_offset=" + queryoffset;
	var endpointURL = instanceAPI + tableQueryStr + reqParams + limit + offSet;
	//gs.log(uniqueTimeFunction() + ": " + endpointURL, "DATA_MIGRATION_LOG");
	moveRecordFromSnowtoSnow(endpointURL);	
}


/*
creates exact replica of record from one isntance to this.
enhanced to cater for table name changes
give it endpoint. it pulls all records.
*/
function moveRecordFromSnowtoSnow(endpoint)
{
	var tableName = endpoint.split("?")[0].split("/")[6];

	var r = new sn_ws.RESTMessageV2();
	r.setEndpoint(endpoint);
	r.setHttpMethod('GET');
	r.setRequestHeader('Content-Type', 'application/json');
	r.setAuthenticationProfile('basic', "c8ac221adb1209900034150505961988");  //sys_auth_profile_basic.do?sys_id=dfa9cebcdb5e8150344b9517f39619d6

	try
	{
		var response = r.execute();	
	}
	catch(e)
	{
		gs.log("ERROR: Sending REST API " + e + "\n\n" + restEndpoint, "DATA_MIGRATION_LOG");
	}


	if (!response.haveError())
	{
		var parsed = JSON.parse(response.getBody());
		var results = parsed.result; 

		results.forEach(function(item)
		{
			var gr = new GlideRecord(tableName);
			gr.initialize();
			for (var key in item) 
			{
				if(key == "sys_id")
				{
					gr.setNewGuidValue(item[key]); //sets sys_id
				}
				else if(key != "sys_tags") // fields to ignore
				{
					//if (['name','table','tablename','parent_table_name','target_table','source_table'].indexOf(key) != -1)  //if the field name is one of the following
					//{
					//	if(item[key] == oldTableName) // if the value is the old table name
					//	{
					//		gr.setValue(key, newTableName);  // set it to the new table name
					//	}
					//	else
					//	{
					//		gr.setValue(key, item[key]); // my mistake - set the value normally
					//	}
					//}
					//else
					//{
						gr.setValue(key, item[key]);
					//}
				}
			}
			gr.autoSysFields(false);
			gr.setWorkflow(false);

			if (gr.insert())
			{
				//record inserted
			}
			else
			{
				gs.log("NOT INSERTED: " + tableName + "." + item.sys_id + " : " + gr.getLastErrorMessage(), "DATA_MIGRATION_LOG");
			}
		});
	}
}

 

this script would be a good base to work from

i have redacted some content from it but should still work good.

 

enhancements can be made if you wanted to change the source and destination tables.
the enhancement would be to pass the old and the new table into the function

function moveRecordFromSnowtoSnow(endpoint,oldTable,newTable)

the un-commenting the items inside the script.

  • 16,400 Views
Comments
DrewW
Mega Sage
‎03-20-2022 09:29 AM

I think you missed one, Instance Data Replication.  It requires a subscription but if you have to replicate do this a lot it maybe worth it.

Instance Data Replication | ServiceNow Docs

 

Viraj Sapte
Tera Expert
‎06-16-2022 01:47 AM

@restevao Could you please tell where to run the script mentioned in point 4.

0 Helpfuls
Version history
Last update:
‎03-20-2022 04:35 AM
Updated by: