ExMeX Framework Release Backup Cleanup Guide

Overview

This guide outlines the steps to clean up release backup data from the ExMeX Framework installation process. The cleanup process removes both DDL backup entries and physical backup tables to free up database space and maintain system performance.

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)
• SQLCMD mode enabled in your SQL client

Important Notes

• Always review what will be deleted before executing the cleanup to avoid any unintended data loss
• The cleanup process includes both DDL backup entries and physical backup tables
• Use dry-run mode first to see what would be deleted before performing actual cleanup
• Ensure no rollback operations are planned for the releases you intend to clean up
• Physical backup tables cannot be restored once deleted

Backup Cleanup Types

The cleanup script supports different types of backup data:

DDL Backups

• Stored in MetadataZoneCore.ExMeXDatabaseObjectsBackupTable
• Contains object definitions for rollback purposes
• Stored in MetadataZoneCore.ExMeXTableConstraintsBackupTable
• Contains constraint and index backup information

Physical Backup Tables

• Actual database tables renamed with _Backup_ pattern during installation
• Consume significant database space
• Cannot be restored once dropped

Step-by-Step Cleanup Instructions

1. Start Management Studio or Visual Studio Code

After opening the tool of your choice - Management Studio or Visual Studio Code - connect to your ExMeX Framework Metadata database.

2. Open the cleanup file

• Navigate to the extracted files of your installation/upgrade. Open the cleanup file install/release/release.cleanup.backup.sql.

3. Configure the Cleanup Script

Before running the script, configure the cleanup options at the top of the file:

-- Cleanup options
DECLARE @CleanupMode NVARCHAR(20) = 'RELEASE'  -- 'RELEASE', 'DATE', 'ALL', 'LIST_ONLY'
DECLARE @EnableDryRun BIT = 1                  -- 1 = Show what would be deleted, 0 = Actually delete

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

-- Date-based cleanup (only used when @CleanupMode = 'DATE')
DECLARE @RetentionDays INT = 90                -- Delete backups older than X days

4. Choose Cleanup Mode

Option A: List Available Releases (Recommended First Step)

DECLARE @CleanupMode NVARCHAR(20) = 'LIST_ONLY'

This will show all available backup releases without deleting anything.

Option B: Clean Specific Release

DECLARE @CleanupMode NVARCHAR(20) = 'RELEASE'
DECLARE @RollbackReleaseId NVARCHAR(50) = N'3.4.0' -- Enter release-ID here

Option C: Clean by Date

DECLARE @CleanupMode NVARCHAR(20) = 'DATE'
DECLARE @RetentionDays INT = 90  -- Delete backups older than 90 days

Option D: Clean All Backups (Use with Caution!)

DECLARE @CleanupMode NVARCHAR(20) = 'ALL'

5. Run in Dry-Run Mode First

Always start with dry-run mode enabled:

DECLARE @EnableDryRun BIT = 1  -- Shows what would be deleted

Execute the script to see what would be cleaned up.

6. Execute Actual Cleanup

If you're satisfied with the dry-run results, disable dry-run mode:

DECLARE @EnableDryRun BIT = 0  -- Performs actual deletion

Execute the script again to perform the actual cleanup.

Examples

Example 1: Clean Backups for Specific Release

You have upgraded ExMeX from release 3.3.0 to 3.4.0 and want to clean up the 3.4.0 backups:

1. First, list available releases:

DECLARE @CleanupMode NVARCHAR(20) = 'LIST_ONLY'

2. Configure for specific release cleanup:

DECLARE @CleanupMode NVARCHAR(20) = 'RELEASE'
DECLARE @EnableDryRun BIT = 1
DECLARE @RollbackReleaseId NVARCHAR(50) = N'3.4.0'

3. Run dry-run, then execute actual cleanup with @EnableDryRun = 0

Example 2: Regular Maintenance Cleanup

For regular maintenance, clean backups older than 6 months:

DECLARE @CleanupMode NVARCHAR(20) = 'DATE'
DECLARE @EnableDryRun BIT = 1
DECLARE @RetentionDays INT = 180  -- 6 months

Verification Queries

After cleanup, you can verify the results:

Check remaining DDL backups:

-- Display remaining backup releases
SELECT
    BackupReleaseId,
    COUNT(*) AS NumberOfObjects,
    MIN(BackupDate) AS EarliestBackup,
    MAX(BackupDate) AS LatestBackup
FROM MetadataZoneCore.ExMeXDatabaseObjectsBackupTable
WHERE BackupReleaseId IS NOT NULL
GROUP BY BackupReleaseId
ORDER BY MIN(BackupDate) DESC

Check remaining physical backup tables:

-- Display remaining backup tables
SELECT
    s.name + '.' + t.name AS FullTableName,
    t.create_date AS CreateDate
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name LIKE '%[_]BACKUP[_]%'
   OR t.name LIKE '%[_]bak[_]%'
   OR t.name LIKE '%[_]backup%'
   OR t.name LIKE '%[_]Backup%'
ORDER BY t.create_date DESC

Safety Recommendations

1. Always use dry-run mode first to review what will be deleted
2. Document which releases you're cleaning before execution
3. Ensure no pending rollback operations for the releases being cleaned
4. Consider keeping at least one recent backup for emergency rollback
5. Run cleanup during maintenance windows to avoid conflicts
6. Monitor database space before and after cleanup to verify space recovery

Troubleshooting

Common Issues:

Script fails with permission errors:

• Ensure you have db_owner or equivalent permissions
• Check if any backup tables are currently in use

Physical tables cannot be dropped:

• Check for foreign key constraints referencing backup tables
• Verify no active connections are using the tables

Unexpected results:

• Review the dry-run output carefully
• Verify the correct release-ID format and spelling

Recovery

If you accidentally delete required backups:

• Physical backup tables cannot be recovered once dropped
• DDL backups in tables can potentially be restored from database backups
• Consider restoring the entire database from backup if critical rollback capability was lost

Important: There is no undo for the cleanup operation. Always ensure you have proper database backups before running cleanup operations.