Target Audience: Advanced User, IT Manager
Article #: 9719
This Reference Article lays out a procedure to restore missing historical data to the dbo.tblHistory table in the LABELARCHIVE database for TEKLYNX CENTRAL or LABEL ARCHIVE.
Most likely, you are reading this article because you have experienced an undocumented feature in TEKLYNX CENTRAL (TC) or LABEL ARCHIVE. This feature automatically deletes the oldest data stored in the dbo.tblHistory table. This occurs when the number of records stored in the table reaches the default maximum of 100,000 records as explained here.
Fortunately, if discovered in a timely manner, this data is usually not completely lost.
Determine What’s Missing
STEP 1. Start SQL Management Studio and connect to the SQL Server hosting the databases for TEKLYNX CENTRAL or LABEL ARCHIVE.
STEP 2. Expand the LABELARCHIVE database and then expand the Tables folder.
STEP 3. Right-click dbo.tblHistory and select Select Top 1000 Rows. This number may be different if you have customized your Management Studio.
STEP 4. You should see the result of the query on the left windowpane. Modify the upper portion of the result window (SQL statement) to have the output sorted by HISTD and click Execute (F5). This should ensure that you see the lowest HISTID number as the first record.
STEP 5. If the HISTID for the first record is 1001, as in the image above, then no data was deleted by the automatic deletion process. However, if you see a number other than 1001, you are missing records. For example, if the first number is 25478, you are missing records 1001 to 25477.
If the results show that you need to restore data to dbo.tblHistory, you will need to determine how far back you’ll need to go to recover the data based on these criteria:
- How quickly was the issue detected?
- How fast does the table dbo.tblHistory grow?
- The automatic delete feature will remove the oldest 5,001 records each time it executes.
Once you’ve determined how much data will need to be recovered, make sure that you have the the following:
- The appropriate back up files for the time period covering the missing records.
- Enough free space in the SQL server to perform a data restore. It is best to restore the data to a separate SQL server to avoid any accidental replacement of current production data with old data. For the purposes of this article, this will be referred to as “Backup SQL” while the original will be “Production SQL”.
- Appropriate permission in the SQL production server to insert data into the database.
- Appropriate permission in Backup SQL to restore the database.
- A backup of your current production database.
On the Backup SQL
Step 1. Restore the earliest available LABELARCHIVE database to the Backup SQL.
Step 2. Using the same procedure you used in the Determine What’s Missing section above, locate the smallest HISTID. This will determine how much data you can recover from the backups.
Step 3. Create a new SQL query using the following statements:
SELECT min([HISTID]) FROM [LABELARCHIVE].[dbo].[tblHistory]
SELECT max([HISTID]) FROM [LABELARCHIVE].[dbo].[tblHistory]
Step 4. The results will give you the first and last HIST in the restored data, providing you with the range of available records that can be restored. For the purposes of this example, let us assume that min = 1001 and max = 50,000 and the missing data is records 1001 through 25477. Therefore, you want to use the following statement to retrieve only the data you need:
SELECT * FROM [LABELARCHIVE].[dbo].[tblHistory]
where HISTID >= 1001 and HISTID <= 25477
On the Production SQL
Step 1. BACK UP YOUR LABELARCHIVE DATABASE!
Step 2. In SQL Management Studio, navigate to the dbo.tblHistory table, as shown in the Determine What’s Missing section.
Step 3. Right-click on dbo.tblHistory and select Edit Top 200 Rows.
Step 4. Scroll down to the bottom of the list in the Results window.
Note:To get the feel of the process, you may prefer to try the following step with one or two records before proceeding to the full set of data.
Step 5. Go back to the “Backup SQL”.
a. In the Result window, copy the entire contents by Selecting All followed by Copy (Ctrl-A, Ctrl-C).
Step 6. Go back to the “Production SQL.”
a. Highlight the last line on the Result window by right-clicking on the “*”
b. Paste (Ctrl-V). Depending on how many records are being pasted, this process may take several minutes. Please be patient.
c. If you receive any errors, the likely cause is that you are trying to replace already existing records in the production database. This should not be a problem as SQL will reject the data. However, this will cause the process to take longer.
Repeat this process until you have restored all the missing data available from the backups.