Condition / Cause

This WIKI applies when data that exists in a backup does not exist in the live version of the data, and the customer wants the data re-inserted. The most common application of this is to re-insert Line Items for Orders and Estimates that may have been lost due to an error during the save process.

Explanation of Process

The query below inserts the identified records from a backup copy into the database. The backup is restored to a folder ..\Cyrious$\SMS\R1 and the missing records inserted into the live data. The records to be inserted must be identified before this query can be used.

Notes:

  • Existing users do not need to exit for these actions to be run.
  • Always backup first! Running a SQL query is a powerful tool and a misplaced comma can result in data loss.
  • The query to identify missing line items can be found sms1_-_repairing_key_violations

Tier 2 Actions

  1. Confirm you have the correct Data folder by looking at the modified date of the files in the data folder.
  2. Backup the Database by
    • Right-clicking on the Data folder
    • Choosing Send To | Compressed File.
    • When complete, name the file “Data YYYY-MM-DD xx.zip”, as in “Data 2015-03-18 JB.zip” where “xx” are the technicians intials
    • Move the back up to the ..\Cyrious$\SMS\Tech Backups folder. Create the folder if it does not exist.
  3. Locate the customer backup files and copy the Backup file to the ..\Cyrious$\SMS\Backups\ folder
  4. Unzip the backup by right-clicking on it and choosing Extract All. Set the path to be a folder named R1 that is a peer of the Data folder, usually C:\Program Files (x86)\Cyrious\SMS\R1 .
  5. Run Database Browser (DBBrowser.exe in the ..\Cyrious$\SMS\Utilities folder).
  6. Click on the SQL tab, then click on Advanced in the middle row of tabs.
  7. Modify and run the SQL below. You will need to adjust or confirm:
    • TableName - The name of the table missing the records, without quotes
    • IDField - The ID field name, such as OrderID
    • IDList - a comma separated list of the IDs to insert, surrounded by parenthesis.
    • DataPath - The UNC path to the data
    • BackupPath - The UNC path to the backup to restore from.
//set variables and run 
// SET VARIABLES AND run
 
Define TableName ORDER Details DATABASE
Define IDField OrderID
Define IDList (45478)
Define DataPath \\SERVERNAME\Cyrious$\sms\DATA
Define BackupPath \\SERVERNAME\Cyrious$\sms\R1
 
INSERT INTO "[DataPath]\[TableName]"
SELECT *
FROM "[BackupPath]\[TableName]"
WHERE [IDField] IN [IDList]
;

- Open SMS and confirm the data was inserted properly. - Close DBBrowser - Delete the /R1 folder. - Let the customer know that if they had those records open, they will need to close the order/estimate/etc and re-open it to see the changes.

Insert 8.6 Line Items into 8.91 Table

Since 8.91 added 2 new fields to the Order Details Database table, ModifiedByComputer & ModifiedDate you have to tack these on to any data pulled from an 8.6 table that needs to go in a 8.91 table. Warning: Since this likely is old data being inserted you should take extra care to make sure that the data you are restoring is the most recent. Check order history if you are unsure.

Define IDList (999999)
 
SELECT * FROM "\\SERVERNAME\Cyrious$\SMS\TempData\"
WHERE ORDERID in [IDLIST]
;RestoredLineItems FALSE
ALTER TABLE "RestoredLineItems"
ADD ModifiedByComputer CHAR(25), ADD ModifiedDate TIMESTAMP
INSERT INTO "Order Details Database"
SELECT * FROM "RestoredLineItems"
DROP TABLE "RestoredLineItems"

Task Information

* Last Updated: 4/17/2015

* Created By: Michael Nolan

* Verified By:

* Applies to All Versions

You could leave a comment if you were logged in.