Archiving Old Stone Edge Data

Overview

Note: This article regarding SQL servers applies to Stone Edge 7.730 or newer versions. Older versions may not work when archiving SQL Servers.

• Once your store has been up and running for some time, you may notice the following:
  • You have a lot of old order data that you no longer need.
  • It has been running slower recently.
  • The database file size is substantially larger.
• These are good reasons to look into the archiving functionality Stone Edge provides.
• The process works for both Access and SQL Databases.
• Archiving will remove old orders and customers from your database to another file.
  • For Access databases -> the backup will be another Access database.
  • For SQL databases -> the backup will be saved as a separate database attached to the server.
• The archived data will be held:
  • Access database -> The same folder as your database
  • SQL database -> where SQL data is stored (set by a system parameter)
• When performing the archive, make sure everyone is out of Stone Edge.
• It would be best if you used the archiving tool built into Stone Edge because of how the tables are set up.
  • For SQL databases, you CAN NOT go into SSMS and use the archiving tool there.

Backup your Database

• Before performing the archive process, many people choose to create a backup of their entire database in case of an error during the process.
  • This will be done automatically for an SQL database as part of the archive process.
• Click HERE to find out how to perform a backup.

Setting Parameters (SQL Database)

• If you are archiving an SQL database, a few parameters must be set before archiving is possible.

1. Main Menu -> Gear Icon (Settings)
2. System Functions -> Set System Parameters
3. Parameter Group = Program
4. Parameter = SQLServerPathData
   1. Click the folder icon to open up a file explorer window.
   2. The path must be where the SQL database is saved.
      15. In this case, it is C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA
   3. Click “Save”
      
5. Parameter = SQLServerPathBackup
   1. Click the folder icon to open up a file explorer window.
   2. The path must be where the SQL stores backups.
      15. In this case, it is C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup
   3. Click “Save.”

Archiving an Access Database

1. Main Menu -> Gear Icon (Settings).
2. Data Functions -> Archive Old Data.
   
3. A pop-up that reviews what will happen when you archive the data will appear.
   
4. Click “OK”
5. Select an archive name.
   1. Default name: Database Name + Archive + Date
6. Click “OK.”
7. A calendar will appear -> Select a date.
   1. All ORDERS from before the date will be archived.
   2. Use the dropdown at the top to change the year.
   3. Click the day desired.
8. Click “OK”
   
9. A calendar will appear ->. Select a date
   1. All CUSTOMERS from before the date will be archived.
   2. Use the dropdown at the top to change the year.
   3. Click the day desired.
10. Click “OK”
11. A Confirmation pop-up with your choices will appear.
    NOTE: The screenshot shows the date to be 01/01/2022 for both dates.
12. Click “OK”
    
13. The archiving process will start.
    1. A progress bar will be on the bottom right of the Access screen.
    2. Depending on the database size, the program may show (Not Responding) DO NOT click around on the program, or you can cause an error.
    3. If you are worried there is no progress, open Task Manager to see if the CPU and Memory that Access is using are changing; that shows the program is still running.
14. A pop-up will appear with an overview.
15. Click “OK”
    
16. The archive will be where your active database is located.

Archiving a SQL Database

1. Main Menu -> Gear Icon (Settings)
2. Data Functions -> Archive Old Data.
   
3. A pop-up that reviews what will happen when you archive the data will appear.
   
4. Click “OK”
5. A calendar will appear ->. Select a date
   1. All ORDERS AND CUSTOMERS from before the date will be archived.
   2. Use the dropdown at the top to change the year.
   3. Click the day desired.
6. Click “OK”
7. Archiving process will start.
   1. A message on the bottom-left of the Access screen will display what is currently happening.
   2. Depending on the database size, the program may show (Not Responding) DO NOT click around on the program, or you can cause an error.
   3. If you are worried that there is no progress, open Task Manager to see if the CPU and Memory Access is using changed values, showing the program is still running.
8. A pop-up will appear when the archive is complete.
9. Click “OK”
   
10. The following will have occurred:
    1. The database will still be connected to your server, with the archived data removed.
    2. The archived data will appear in your database list, which can be exported as a .bak file.
       1. Database name default: Database Name + Archived + Date (yyyymmdd)
    3. A backup file of the original database will be created.
       1. File name default: Database Name + Backup + Date (yyyymmdd) + [h]mmss
       2. The file path will be where SQLServerPathBackup is set to (can be moved afterward).

Accessing Archived Data

Access Archive

• Open the archived file using the “Switch Stores” button in Stone Edge.
• Click the folder icon to open file explorer and locate the database.

SQL Archive

• The archived data should already be listed as an active database in SSMS
  • restore the .bak file in SSMS.
• Create an ODBC connection to the restored database.
• Use the “Switch Stores” button in Stone Edge and go to “System DSNs” to find the ODBC connection.
• The following message will appear when accessing the archived data.
  

Troubleshooting

• Error 3146 ODBC Call Failed
  
  • For SQL databases, when the paths are not set correctly.
  • Redo the system parameters and try again.
• Timeout Error
  • Find the system parameter SQLServerPathODBCTimeout.
  • Set parameter value = 0 and save.