Skip to main content
Version: 3.4.0 (Latest Stable)

ExMeX Framework Rollback Guide

Overview​

This guide outlines the steps to rollback the ExMeX Framework to a previous version. The rollback process uses an enhanced three-phase approach to ensure complete restoration of database objects and their dependencies.

Prerequisites​

  • Administrative access to the ExMeX Framework Metadata database
  • SQL Server Management Studio or Visual Studio Code with SQL extension
  • Database backup permissions (db_owner or equivalent)
  • Access to the original installation/upgrade files
  • SQLCMD mode enabled in your SQL client

Important Notes​

  • Always back up your configuration and database before proceeding with the rollback to avoid any potential data loss
  • The rollback process may require system downtime (estimated 1-30 minutes depending on database size)
  • Ensure all users are logged out of the system before starting the rollback
  • Verify that no other processes (e.g. batches or modules) are accessing the database during rollback
  • Only one backup per ExMeX release is kept
  • New Foreign Key constraints created after the backup will be permanently removed (this is intended rollback behavior)

Enhanced Rollback Process​

The rollback script uses a three-phase approach:

Phase 0: Foreign Key Analysis and Cleanup​

  • Identifies all Foreign Key constraints referencing tables being restored
  • Distinguishes between backed-up FKs and new FKs created after the backup
  • Removes all current FK constraints to prevent restoration conflicts
  • Documents new FKs for audit purposes (they will NOT be restored)

Phase 1: Object Restoration​

  • Restores database objects (tables, procedures, views, functions, triggers)
  • Uses backup object renaming when available, falls back to definition restoration
  • Handles constraint renaming for tables (Primary Keys, Check Constraints, Default Constraints, Indexes)

Phase 2: Parent Table FK Restoration​

  • Restores Foreign Key constraints FROM restored tables TO other tables

Phase 3: Child Table FK Restoration​

  • Restores ONLY originally backed-up Foreign Key constraints from child tables to restored parent tables
  • New FKs created after backup are intentionally NOT restored

Step-by-Step Rollback Instructions​

1. Prepare the Environment​

  • Stop all ExMeX Framework services if applicable
  • Ensure exclusive access to the metadata database
  • Start SQL Server Management Studio or Visual Studio Code
  • Connect to your ExMeX Framework Metadata database

2. Identify Available Rollback Versions​

Before proceeding, identify which backup releases are available for rollback:

-- Display all available backup releases
SELECT
    BackupReleaseId,
    COUNT(*) AS NumberOfObjects,
    MIN(BackupDate) AS BackupTimestamp
FROM MetadataZoneCore.ExMeXDatabaseObjectsBackupTable
GROUP BY BackupReleaseId
ORDER BY MIN(BackupReleaseId) DESC

3. Open and Configure the Rollback Script​

  • Navigate to the extracted files of your installation/upgrade
  • Open the rollback file install/release/release.rollback.sql
  • IMPORTANT: Enable SQLCMD mode in your SQL client
  • Verify and configure the rollback parameters:
-- Set the path to your ExMeX Framework Core root directory, e.g.:
--      :setvar InitPath "/GitRepos/ExMeX-Framework-Core"
:setvar InitPath "[YourRootDirectory]"

-- Replace 'Release-ID' with the desired release-ID
DECLARE @RollbackReleaseId NVARCHAR(50) = N'Release-ID' -- Enter release-ID here

-- Configuration flags
DECLARE @EnableConstraintRestore BIT = 1         -- 1 = Restore FK/Indexes, 0 = Simple restore only

Critical Configuration Notes:

  • The InitPath variable must point to your ExMeX Framework Core root directory
  • The script will attempt to include RollbackManualEntries.{ReleaseVersion}.sql from the install/release directory
  • Set @EnableConstraintRestore = 0 if you want to restore only objects without FK constraints

4. Execute the Rollback Script​

  • Double-check that the rollback release-ID is correctly set
  • Double-check that the ExMeX Framework Core root directory is correctly set
  • Ensure SQLCMD mode is enabled - this is critical for the script to work
  • Execute the complete rollback script
  • Monitor the execution for any error messages

5. Verify Rollback Success​

After the rollback completes, verify the operation:

-- Check current framework version (ExMeX release >= 3.4.0)
SELECT TOP 1 ReleaseVersion, ReleaseDate
FROM MetadataZoneCore.ExMeXVersionHistory
ORDER BY ReleaseInstallationTimestamp DESC

-- Check current framework version (ExMeX release = 3.3.0)
SELECT TOP 1 ReleaseVersion, ReleaseCreatedDate
FROM MetadataZone.TEDAMOHExMeXRelease
ORDER BY ReleaseInstalledDate DESC

-- Verify no backup objects remain
-- Zero records should be returned
SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS BackupObjectName,
    type_desc,
    create_date
FROM sys.objects
WHERE name LIKE '%_Backup_[Release-ID]'
-- e.g.
--WHERE name LIKE '%_Backup_3.4.0'
ORDER BY create_date DESC

Example Scenario​

Situation: You have upgraded ExMeX from version 3.3.0 to 3.4.0, and need to rollback to 3.3.0.

  1. The installation created a backup with release-ID '3.4.0'
  2. To rollback to version 3.3.0, set @RollbackReleaseId = N'3.4.0' in the rollback script
  3. This will restore all objects from the 3.4.0 backup, effectively returning you to version 3.3.0
  4. Any Foreign Keys created after the 3.4.0 upgrade will be permanently removed

Advanced Verification and Troubleshooting​

Detailed Release Analysis​

-- Display details of a specific release
DECLARE @ReleaseToShow NVARCHAR(50) = N'Release-ID' -- Enter release-ID here
SELECT
    BackupId,
    SchemaName + N'.' + ObjectName AS FullObjectName,
    ObjectType,
    CASE ObjectType
        WHEN 'P' THEN 'Stored Procedure'
        WHEN 'FN' THEN 'Scalar Function'
        WHEN 'IF' THEN 'Inline Function'
        WHEN 'TF' THEN 'Table Function'
        WHEN 'V' THEN 'View'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'U' THEN 'User Table'
        ELSE 'Other'
    END AS ObjectTypeDescription,
    BackupDate,
    LEN(ObjectDefinition) AS DefinitionLength
FROM MetadataZoneCore.ExMeXDatabaseObjectsBackupTable
WHERE BackupReleaseId = @ReleaseToShow
ORDER BY ObjectType, BackupId

Constraint Analysis​

-- Display table constraints for a specific release
DECLARE @ReleaseToShow NVARCHAR(50) = N'Release-ID' -- Enter release-ID here
SELECT
    TableName,
    SchemaName,
    ConstraintType,
    CASE ConstraintType
        WHEN 'FK' THEN 'Foreign Key (from restored table)'
        WHEN 'FK_CHILD' THEN 'Foreign Key (from child table to restored parent)'
        WHEN 'FK_NEW_DROPPED' THEN 'NEW FK (was dropped, not restored)'
        WHEN 'PK_RENAME' THEN 'Primary Key (renamed back)'
        WHEN 'CK_RENAME' THEN 'Check Constraint (renamed back)'
        WHEN 'DF_RENAME' THEN 'Default Constraint (renamed back)'
        WHEN 'IX_RENAME' THEN 'Index (renamed back)'
        ELSE ConstraintType
    END AS ConstraintTypeDescription,
    ConstraintName,
    LEFT(ConstraintDefinition, 100) AS DefinitionPreview,
    BackupDate
FROM MetadataZoneCore.ExMeXTableConstraintsBackupTable
WHERE BackupReleaseId = @ReleaseToShow
ORDER BY TableName, ConstraintType

FK Restoration Verification​

-- Check which FKs were NOT restored (troubleshooting)
SELECT
    'Parent FK not restored' AS IssueType,
    t.TableName,
    t.SchemaName,
    t.ConstraintName,
    'FK from restored table not found in sys.foreign_keys' AS Issue
FROM MetadataZoneCore.ExMeXTableConstraintsBackupTable t
WHERE t.ConstraintType = 'FK'
  AND t.BackupReleaseId = N'Release-ID' -- Replace with your release ID
  AND NOT EXISTS (
      SELECT 1 FROM sys.foreign_keys fk
      INNER JOIN sys.tables tb ON fk.parent_object_id = tb.object_id
      WHERE fk.name = t.ConstraintName
        AND tb.name = t.TableName
        AND tb.schema_id = SCHEMA_ID(t.SchemaName)
  )
UNION ALL
SELECT
    'Child FK not restored' AS IssueType,
    t.TableName,
    t.SchemaName,
    t.ConstraintName,
    'FK from child table to parent not found' AS Issue
FROM MetadataZoneCore.ExMeXTableConstraintsBackupTable t
WHERE t.ConstraintType = 'FK_CHILD'
  AND t.BackupReleaseId = N'Release-ID' -- Replace with your release ID
  AND NOT EXISTS (
      SELECT 1 FROM sys.foreign_keys fk
      INNER JOIN sys.tables tb ON fk.parent_object_id = tb.object_id
      WHERE fk.name = t.ConstraintName
        AND tb.name = t.TableName
        AND tb.schema_id = SCHEMA_ID(t.SchemaName)
  )

Post-Rollback Steps​

  1. Restart Services: Restart all ExMeX Framework services
  2. Clear Caches: Clear application caches if applicable
  3. Test Functionality: Perform comprehensive functionality tests
  4. Verify Data Integrity: Check that all expected data is accessible
  5. Review FK Dependencies: Verify that all necessary Foreign Key relationships are restored
  6. Notify Users: Inform users that the system is available again
  7. Update Documentation: Document the rollback for audit purposes

Troubleshooting​

Common Issues​

SQLCMD Errors​

  • Symptom: Script fails with variable substitution errors
  • Solution: Ensure SQLCMD mode is enabled in your SQL client
  • Alternative: Manually replace variables in the script if SQLCMD is not available

Permission Errors​

  • Symptom: Access denied during object restoration
  • Solution: Ensure you have sufficient database permissions (db_owner or equivalent)

Missing Backup Data​

  • Symptom: "No backup found for release" error
  • Solution: Verify the backup release exists using the available releases query

FK Restoration Failures​

  • Symptom: Foreign Key constraints not properly restored
  • Solution: Use the FK verification queries to identify specific issues
  • Manual Fix: Use the manual FK restoration script generator (included in utility scripts)

Configuration Issues​

Incorrect InitPath​

  • Symptom: Cannot find RollbackManualEntries.{Version}.sql
  • Solution: Verify the InitPath points to the correct ExMeX Framework Core root directory
  • Workaround: Create an empty file or comment out the :r statement if the file doesn't exist

Release Version Mismatch​

  • Symptom: Objects not properly restored or version still shows new release
  • Solution: Double-check the @RollbackReleaseId matches an available backup release

Rollback Failure Recovery​

If the rollback fails:

  1. Stay Calm: Your original backup should still be intact
  2. Check Error Messages: Identify the specific phase and object causing issues
  3. Partial Recovery: Some objects may have been successfully restored
  4. Database Restore: If necessary, restore from your pre-rollback database backup
  5. Contact Support: Provide detailed error logs and script output

Backup Retention Policy​

  • Rollback backups are typically retained for 360 days after creation
  • Older backups may be automatically purged based on retention policy
  • Consider creating manual backups for long-term retention needs
  • Each backup includes both object definitions and constraint relationships

Performance Considerations​

  • Downtime Estimation: 1-30 minutes depending on database size and complexity
  • FK Constraint Count: More Foreign Keys = longer restoration time
  • Object Count: Large numbers of procedures/functions increase processing time
  • Hardware Impact: Database size and server performance affect duration

Note: This enhanced rollback process provides comprehensive restoration capabilities while maintaining safety through careful FK analysis and phased restoration. The process is designed to handle complex database relationships and provide clear feedback on restoration status.