On running Test To Live you see the following error after pressing the Sync button:
Syncronisation failed. You must restore your Live database from backup to avoid potential data corruption. See the log file for details.
This error generally occurs when there have been design changes made in the Live database. As per the documentation, once a Test database has been created from the Live database then no design changes should be made in the Live system. Test To Live will attempt to revert any changes made however due to the referential nature of the Service Desk database schema this is not always possible and causes the error above.
In the first instance you should restore the Live database from the backup taken before running Test To Live. Then use the following steps to find what has caused the synchronisation to fail:
1. View the log file created during the synchronisation. To do this either press the View Log button or browse to C:\Users\<logged-in-user>\AppData\Local\LANDesk\TestToLive and look for the latest file.
2. At the end of the log file you will see the full database error message generated by SQL Server such as:
[10:08:19] SyncData - Data Sync - Sync failed: The DELETE statement conflicted with the REFERENCE constraint "fkey$im_incident$in_cat". The conflict occurred in database "ootb_eng", table "dbo.im_incident", column 'im_category_guid'.. [10:08:19] SyncData - Data Sync - Writing sync SQL to 'C:\Users\Administrator\AppData\Local\LANDesk\TestToLive\TestToLive - 2014-01-08 10.07.05.sql'... [10:08:19] SyncData - Data Sync - Sync SQL saved.
There are two important parts to the above. First is the database error and second is the SQL file you might need later on. There is generally only one type of error reported here which is in the format "The DELETE (or ALTER) statement conflicted with the REFERENCE constraint..." so the rest of this document covers that. If you see a different error please contact your local support for further assistance.
Also note that the error reported may not be the only issue stopping Test To Live, it is simply the first error that SQL Server came across. Resolving the error in the log may lead to Test To Live failing again with a different error.
Resolving "REFERENCE constraint" errors
The example error above is a constraint error which means that Test To Live has attempted to delete something in the Live database that is being referenced somewhere else.
The main cause of this is when some design changes have been made in the Live database, however there are some other situations detailed later in this document. Often this is something small like adding a new Support Group, Category or other reference object, and in these cases it can be easy to fix. If larger design changes have been made such as object, window, process or query changes this will likely be far too complex to fix and unfortunately the Live database is not in a compatible state for use with Test To Live.
Please refer to the Test To Live documentation for information on what counts as design data. No changes to these areas should be made in the Live database.
Common constraint - fkey$tps_scheduled_result$a
This can occur if you have deleted a Scheduled Bulk Action or Scheduled Calculation in the Test system. Or if you have created new ones in the Live system. The fix is to manually compare the items in Schedule Manager between the Test and Live systems and delete any actions that appear in the Live system but not the Test system. Then re-run Test To Live.
Common constraint - fkey$ui_shortcut_item$grp
This can occur when Test To Live is attempting to delete a Shortcut Group that contains a "user" shortcut. The fix is to manually remove the shortcut or the entire shortcut group from the Live system. Then re-run Test To Live.
Common constraint - fkey$cf_ci$template
This can occur if you have removed a template from a Configuration Item and then deleted the template in your Test Database before running Test To Live. Test To Live will attempt to delete the Template from Live but since this is used on a none design business object, the Configuration Item will not be updated and the constraint will not be temporarilly disabled.
To solve this, run a Query in Live to see all Configuration Items with the relevant template selected and then clear this on all Configuration Items.
Then re-run Test To Live.
Using the following steps you can identify which data Test To Live is attempting to remove.
Note: If any of the steps or SQL commands fail please contact Support for manual assistance.
Step 1. Find the database table the records are in
Note that this normally isn't as simple as looking at the table name given in the error message. The name in the error is normally for the table that references the records in question. To find the table that holds the records themselves perform the following steps:
a. Run the attached SQL script named sp_FindObjectsFromForeignKey.sql against the Live database.
b. Run the following SQL command using the name of the constraint in the error message:
exec sp_FindObjectsFromForeignKey '<Foreign Key Name>'
For example the name of the constraint in the error above is fkey$im_incident$in_cat so the SQL command to use is:
exec sp_FindObjectsFromForeignKey 'fkey$im_incident$in_cat'
This will output the following:
Foreign Key: fkey$im_incident$in_cat Object: Incident Management -> Category Database Table: im_incident_category Referenced From Object: Incident Management -> Incident Reference Attribute: <unknown> Database Table: im_incident Database Column: im_category_guid
This tells us that the object is Category inside the Incident Management module which is in the im_incident_category table of the database.
It also tells us that the place that is referencing the record(s) that Test To Live is trying to delete is an attribute on the Incident object. It is not always possible to find the name of the attribute but the command also gives the name of the database column so from that you should be able to figure out the attribute. In the example above this is the Category attribute on Incident that is referencing Category records that Test To Live wants to delete.
Step 2. Find the records
Now we know where the records are we can find the records themselves using the .sql file referred to in the error message. This file contains all the SQL commands that were used during the data synchronisation. It also includes comments to help read the commands. If there have been a lot of changes made in Test (or in Live!) this file could be quite big but with the information gathered so far it is easy to find the relevant section. The file can be opened in Notepad or any other text editor.
The commands are split into the following sections:
- Disable reference constraints required to alter all the data that has changed.
- Delete data that is not present in the Test database.
- Update data that is different in the Test database.
- Insert data that is not present in the Live database.
- Re-enable the reference constraints.
The 2nd section is usually the cause of the constraint errors - deleting data in Live. Run the following steps to find the records Test To Live is attempting to delete:
a. Open the .sql file in a text editor such as Notepad.
b. Search for the text DELETE FROM - this will take us to the top of the 2nd section.
c. Search for the table name the records live in, for example im_incident_category.
You will find a section similar to this:
-- Delete 1 row from [dbo].[im_incident_category] DELETE FROM [dbo].[im_incident_category] WHERE [im_guid]='05f95ebc-4921-497e-a4f0-588fcda07ddf'
The first line is a comment that states how many records will be deleted. The next line(s) are the SQL commands to delete the records. There may be multiple lines to delete multiple records. One or more of the records is causing the error in Test To Live.
Step 3. Remove all references to the records
Test To Live wants to delete these records because they don't exist in the Test database. It is failing because those records are referenced somewhere, for instance with the example above the Category record has been used on an Incident. The only way to solve the error and allow Test To Live to synchronise successfully is to remove all references to those records in the Live database.
In the case of a category, reference list, or other "look up" objects this is quite straight forward. If however the previous step refer you to an object in the Metadata, Lifecycle, or User Interface modules, or to an object you don't recognise, this is likely due to more major design changes made in the Live database. In this scenario it is very unlikely you will be able to undo the changes made and unfortunately the Live database is not in a compatible state for Test To Live.
To see which records Test To Live is attempting to delete and to see where they are referenced run the following steps for each record in the log file:
a. Run the attached SQL script named sp_FindRecordsFromForeignKey.sql against the Live database.
b. Run the following SQL command using the name of the constraint in the error message and the Guid of each record:
exec sp_FindRecordsFromForeignKey '<Foreign Key Name>', '<Record Guid>'
For example the name of the constraint in the error above is fkey$im_incident$in_cat and the Guid of the record is 05f95ebc-4921-497e-a4f0-588fcda07ddf so the SQL command to use is:
exec sp_FindRecordsFromForeignKey 'fkey$im_incident$in_cat' '05f95ebc-4921-497e-a4f0-588fcda07ddf'
This will output the following:
Object: Incident Management -> Category Record: Hardware Referenced From Object: Incident Management -> Incident Record(s): -- 9 -- 13 End of records.
This shows us that the record in question is called Hardware and that it is currently referenced on incidents 9 and 13.
c. Open each referencing record in Service Desk and change the reference to something else. If the record is read only (for example a closed incident) you can either re-open it temporarily or make the change manually in the database.