Legacy Database Migration AI Prompts for Data Engineers

TL;DR

• Legacy database migrations carry risks that modern development practices do not account for
• undocumented stored procedures and tribal knowledge create the biggest migration challenges
• AI prompts help translate legacy SQL, generate schema mappings, and automate documentation
• Data validation is more critical than code migration—verify everything
• Migration success requires careful rollback planning
• AI assists migration but cannot replace understanding of business logic

Introduction

Legacy database migrations are among the highest-risk operations in data engineering. Unlike greenfield database design where you control everything, migrations involve legacy systems where undocumented code, tribal knowledge, and business-critical dependencies create minefields that can turn routine upgrades into production incidents. SQL Server 2008 reaching end-of-support created urgency for many organizations, but that urgency sometimes led to rushed migrations that introduced more problems than they solved.

The challenge is not just technical. Legacy databases often contain business logic encoded in stored procedures that nobody fully understands anymore. Queries were optimized for database versions or hardware that no longer exist. Data schemas reflect business rules that may have changed but were never updated in the database. And the people who wrote the original code are long gone, leaving behind systems that work but cannot be explained.

AI-assisted database migration offers new capabilities for managing these challenges. When prompts are designed effectively, AI can help analyze legacy SQL code, generate schema conversion scripts, document business logic, and create validation checks. This guide provides AI prompts specifically designed for data engineers who want to use AI to reduce migration risk and ensure data integrity.

Table of Contents

1. Migration Planning Foundations
2. Schema Analysis and Conversion
3. Stored Procedure Migration
4. Data Migration Strategy
5. Validation and Verification
6. Rollback Planning
7. FAQ: Legacy Database Migration

---

Migration Planning Foundations {#planning}

Successful migrations begin with thorough planning.

Prompt for Migration Assessment:

Assess legacy database for migration:

DATABASE CONTEXT:
- Source database: [DESCRIBE]
- Target platform: [DESCRIBE]
- Migration scope: [DESCRIBE]

Assessment framework:

1. INVENTORY DOCUMENTATION:
   - What tables exist in the database?
   - What views, stored procedures, and functions?
   - What triggers and constraints exist?
   - What indexes and performance artifacts?
   - What security objects (users, roles, permissions)?

2. DEPENDENCY MAPPING:
   - What applications connect to this database?
   - What are the connection strings?
   - What features or functions does each application use?
   - What is the criticality of each application?
   - What testing is required for each dependent application?

3. COMPLEXITY ASSESSMENT:
   - What percentage of code is documented?
   - What percentage of code is understood?
   - What business logic is embedded in the database?
   - What custom SQL exceeds modern idiom?
   - What legacy features or deprecated syntax?

4. RISK IDENTIFICATION:
   - What would fail if migration fails?
   - What data cannot be lost?
   - What downtime is acceptable?
   - What rollback capabilities exist?
   - What are the highest-risk migration components?

Assess thoroughly before committing to migration approach.

Prompt for Migration Approach Selection:

Select migration approach:

MIGRATION OPTIONS:
- Lift and shift: [DESCRIBE]
- Replatform: [DESCRIBE]
- Refactor: [DESCRIBE]

Approach framework:

1. LIFT-AND-SHIFT ANALYSIS:
   - When is this approach appropriate?
   - What compatibility layers needed?
   - What performance implications?
   - What is the effort vs risk tradeoff?
   - When to choose this over refactoring?

2. REPLATFORM ANALYSIS:
   - When is this approach appropriate?
   - What minimal changes to enable migration?
   - What deprecated feature remediation?
   - What performance tuning after migration?
   - What testing requirements?

3. REFACTOR ANALYSIS:
   - When is this approach appropriate?
   - What business logic to rewrite?
   - What schema redesign needed?
   - What application changes required?
   - What is the total effort?

4. SELECTION CRITERIA:
   - What downtime constraints apply?
   - What budget and timeline constraints?
   - What risk tolerance exists?
   - What team capabilities available?
   - What stakeholder expectations?

Select approach that balances risk, effort, and outcomes.

---

Schema Analysis and Conversion {#schema}

Schema migration requires understanding semantic equivalence, not just syntax.

Prompt for Schema Conversion:

Convert database schema:

SOURCE SCHEMA:
- Source tables: [LIST]
- Source constraints: [LIST]

Conversion framework:

1. TABLE MAPPING:
   - What tables map directly to target?
   - What tables require transformation?
   - What tables are obsolete and can be dropped?
   - What tables need consolidation?
   - What new tables required in target?

2. DATA TYPE MAPPING:
   - What source data types map to target?
   - What precision/length changes needed?
   - What deprecated types to replace?
   - What Unicode or encoding changes?
   - What temporal type handling (DATE, DATETIME, TIMESTAMP)?

3. CONSTRAINT TRANSLATION:
   - What PRIMARY KEY constraints map?
   - What FOREIGN KEY constraints map?
   - What CHECK constraints translate?
   - What UNIQUE constraints map?
   - What DEFAULT constraints translate?

4. INDEX MIGRATION:
   - What indexes directly translate?
   - What indexes are obsolete?
   - What new indexes for performance?
   - What index names and definitions?
   - What clustered vs non-clustered decisions?

Convert schemas that preserve semantic equivalence.

Prompt for Constraint and Relationship Analysis:

Analyze database constraints:

CONSTRAINT ANALYSIS:
- Tables to analyze: [LIST]
- Known issues: [LIST]

Constraint framework:

1. REFERENTIAL INTEGRITY:
   - What foreign key relationships exist?
   - What cascading rules are defined?
   - What circular references exist?
   - What legacy relationships no longer apply?
   - What constraints enforce business rules?

2. BUSINESS RULE CONSTRAINTS:
   - What CHECK constraints exist?
   - What triggers enforce business logic?
   - What assumptions embedded in constraints?
   - What business rules documented vs undocumented?
   - What constraints might fail in new environment?

3. DATA QUALITY CONSTRAINTS:
   - What NULL constraints exist?
   - What unique constraints exist?
   - What value ranges are enforced?
   - What pattern matching is enforced?
   - What historical artifacts remain?

4. MIGRATION RISK:
   - What constraints might fail on migration?
   - What data violates constraints?
   - What remediation needed?
   - What can be cleaned before vs after migration?
   - What represents actual business rules vs technical debt?

Analyze constraints that enforce business rules vs technical artifacts.

---

Stored Procedure Migration {#procedures}

Stored procedures contain the most undocumented business logic.

Prompt for Stored Procedure Analysis:

Analyze stored procedures for migration:

PROCEDURE CONTEXT:
- Procedures to analyze: [LIST]
- Business criticality: [DESCRIBE]

Analysis framework:

1. FUNCTIONAL ANALYSIS:
   - What does this procedure do?
   - What are the inputs and outputs?
   - What side effects does it have?
   - What business rules does it enforce?
   - What error handling exists?

2. DEPENDENCY ANALYSIS:
   - What tables does this procedure access?
   - What other procedures does it call?
   - What functions does it use?
   - What global variables or settings?
   - What external systems does it touch?

3. LEGACY SYNTAX:
   - What deprecated syntax exists?
   - What version-specific features?
   - What ANSI SQL vs proprietary extensions?
   - What performance patterns that no longer apply?
   - What undocumented behaviors relied upon?

4. DOCUMENTATION GAPS:
   - What is documented vs undocumented?
   - What does the code reveal about intent?
   - What can be inferred from usage patterns?
   - What requires business user input?
   - What can be reverse-engineered from data?

Analyze procedures that contain undocumented business logic.

Prompt for SQL Modernization:

Modernize legacy SQL:

LEGACY SQL:
- Current SQL: [DESCRIBE]
- Target dialect: [DESCRIBE]

Modernization framework:

1. SYNTAX CONVERSION:
   - What proprietary syntax to convert?
   - What deprecated functions to replace?
   - What implicit conversions to make explicit?
   - What joins to modernize (old-style vs ANSI)?
   - What set operations to use?

2. PERFORMANCE PATTERNS:
   - What table hints to remove?
   - What query hints to add?
   - What deprecated performance patterns?
   - What cursor usage to convert to set-based?
   - What while loops to convert?

3. TEMPORAL HANDLING:
   - How to handle DATE vs DATETIME vs DATETIME2?
   - What date math to modernize?
   - What timezone handling?
   - What business date vs system date?
   - What historical date handling?

4. ERROR HANDLING:
   - What legacy error handling patterns?
   - What TRY-CATCH blocks to add?
   - What custom error messages to preserve?
   - What transaction handling to add?
   - What logging to add?

Modernize SQL that preserves behavior while improving maintainability.

---

Data Migration Strategy {#data}

Data is the most critical asset—migration must preserve it exactly.

Prompt for Data Migration Planning:

Plan data migration:

MIGRATION CONTEXT:
- Data volume: [DESCRIBE]
- Migration scope: [DESCRIBE]

Migration framework:

1. DATA INVENTORY:
   - What tables to migrate?
   - What data volumes per table?
   - What is the growth rate?
   - What archived or historical data?
   - What staging or temporary tables?

2. MIGRATION SEQUENCING:
   - What tables have no dependencies?
   - What tables depend on other tables first?
   - What referential integrity to maintain?
   - What order prevents constraint failures?
   - What parallel migration opportunities?

3. DATA TRANSFORMATION:
   - What data requires transformation?
   - What encoding conversions?
   - What date/time normalization?
   - What value mapping?
   - What cleanup before migration?

4. DOWNTIME PLANNING:
   - What is acceptable downtime window?
   - What can be migrated incrementally?
   - What requires cutover vs continuous replication?
   - What application deployment coordination?
   - What rollback window if migration fails?

Plan data migration that preserves integrity.

Prompt for Incremental Migration Strategy:

Design incremental data migration:

INCREMENTAL CONTEXT:
- Data size: [DESCRIBE]
- Downtime constraints: [DESCRIBE]

Incremental framework:

1. CUTOVER STRATEGY:
   - What data migrates at cutover vs before?
   - What delta changes need migration?
   - What is the final sync process?
   - What is the cutover verification?
   - What is the rollback trigger?

2. CHANGE DATA CAPTURE:
   - What CDC approach for incremental changes?
   - What source triggers or logs?
   - What change tables or queues?
   - What latency tolerance?
   - What happens if CDC fails?

3. VALIDATION STRATEGY:
   - What row counts to verify?
   - What checksum validation?
   - What sampling for detail validation?
   - What application-level validation?
   - What user acceptance testing?

4. FALLBACK PLAN:
   - What triggers fallback?
   - How to reverse incremental changes?
   - What is the application rollback?
   - What is the communication plan?
   - What is the post-mortem process?

Design incremental migration with rollback capability.

---

Validation and Verification {#validation}

Validation ensures data integrity is preserved.

Prompt for Validation Framework:

Develop migration validation:

VALIDATION SCOPE:
- Tables to validate: [LIST]
- Validation approach: [DESCRIBE]

Validation framework:

1. ROW COUNT VALIDATION:
   - What source and target row counts?
   - What tables must match exactly?
   - What tolerance for growth tables?
   - What reconciliation process?
   - What thresholds for investigation?

2. DATA SAMPLING:
   - What sampling strategy per table size?
   - What random sampling vs deterministic?
   - What sample size for statistical confidence?
   - What manual spot-check verification?
   - What tooling for comparison?

3. CHECKSUM VALIDATION:
   - What hash-based validation?
   - What column-level vs row-level?
   - What handling of NULLs?
   - What performance impact of checksums?
   - What reconciliation process?

4. BUSINESS LOGIC VALIDATION:
   - What business rules to verify?
   - What aggregation or totals to check?
   - What date range validations?
   - What referential integrity checks?
   - What application-level validation?

Validate that data arrived correctly.

Prompt for Post-Migration Verification:

Verify post-migration data integrity:

POST-MIGRATION CONTEXT:
- Migration completed: [DESCRIBE]
- Issues identified: [LIST]

Verification framework:

1. IMMEDIATE VERIFICATION:
   - What row counts to verify immediately?
   - What checksums to run on critical tables?
   - What application smoke tests?
   - What user-reported issues to investigate?
   - What monitoring to watch?

2. PERFORMANCE VERIFICATION:
   - What query performance comparison?
   - What execution plan differences?
   - What index effectiveness?
   - What blocking or deadlocks?
   - What baseline comparison to source?

3. APPLICATION VERIFICATION:
   - What application functionality tests?
   - What end-to-end business process tests?
   - What integration tests with downstream systems?
   - What reporting and analytics validation?
   - What user acceptance testing?

4. LONG-TERM MONITORING:
   - What metrics to monitor post-migration?
   - What error rate monitoring?
   - What performance baselines?
   - What data quality monitoring?
   - What health check procedures?

Verify migration that satisfies stakeholders.

---

Rollback Planning {#rollback}

Rollback planning is essential—hope for success, plan for failure.

Prompt for Rollback Strategy:

Develop rollback strategy:

MIGRATION PLAN:
- Migration approach: [DESCRIBE]
- Cutover plan: [DESCRIBE]

Rollback framework:

1. ROLLBACK TRIGGERS:
   - What issues trigger rollback?
   - What severity threshold for rollback?
   - What timeline post-migration allows rollback?
   - What is the decision-making process?
   - Who has authority to rollback?

2. ROLLBACK PROCEDURES:
   - What is the rollback procedure?
   - What data to preserve for rollback?
   - What application state to restore?
   - What DNS or connectivity changes?
   - What is the step-by-step rollback?

3. DATA ROLLBACK:
   - What data changes require reversal?
   - What backup to restore?
   - What point-in-time recovery?
   - What validation after rollback?
   - What is the data rollback timeline?

4. COMMUNICATION PLAN:
   - Who to notify of rollback?
   - What stakeholder communication?
   - What is the rollback timeline?
   - What is the estimated duration?
   - What is the next attempt timeline?

Plan rollback that can execute quickly if needed.

Prompt for Risk Mitigation:

Mitigate migration risks:

RISK CONTEXT:
- Identified risks: [LIST]
- Migration approach: [DESCRIBE]

Risk mitigation framework:

1. TECHNICAL RISKS:
   - What if data volume exceeds estimates?
   - What if performance degrades significantly?
   - What if application compatibility issues?
   - What if security settings incorrect?
   - What if connectivity issues?

2. OPERATIONAL RISKS:
   - What if migration window exceeded?
   - What if resource constraints appear?
   - What if key personnel unavailable?
   - What if tooling fails?
   - What if documentation inadequate?

3. BUSINESS RISKS:
   - What if downstream systems affected?
   - What if customer data affected?
   - What if SLA breaches occur?
   - What if regulatory issues arise?
   - What if reputational damage occurs?

4. CONTINGENCY PLANNING:
   - What contingency triggers activate?
   - What alternative approaches available?
   - What resources can be reallocated?
   - What escalation paths exist?
   - What lessons learned for future?

Mitigate risks that could derail migration.

---

FAQ: Legacy Database Migration {#faq}

What is the biggest risk in legacy database migration?

Data loss or corruption is the highest-risk outcome. The safest approach is migrating to a new platform while keeping the old system running in parallel, verifying data integrity thoroughly before cutover, and maintaining rollback capability until you are certain the migration succeeded. Rushing migrations to meet deadline pressure has caused more migration failures than any technical issue.

How do we handle undocumented stored procedures?

Start by analyzing what each procedure does through code review, usage pattern analysis, and correlation with application behavior. Document what you learn as you go. For critical business logic, have business users review and validate that the logic still applies. Consider whether the stored procedure logic should be moved to application code where it is easier to test and maintain. Never migrate stored procedures you do not understand without thorough testing.

How do we validate that data migrated correctly?

Use multiple validation approaches: row counts per table, checksum or hash validation on critical columns, sampling and manual comparison of individual records, application-level validation through existing business logic, and user acceptance testing. Validate not just that data exists but that it is correct. Set explicit thresholds for acceptable variance and investigation procedures when thresholds are exceeded.

What should we do if we find data quality issues during migration?

Document all data quality issues before migration. Cleanse data in the source system if possible before migration, or cleanse during migration with documented transformation rules. Some data quality issues may be acceptable to defer, but all should be documented and addressed according to priority. Never migrate known bad data silently—make quality decisions explicit.

How do we minimize downtime during migration?

Use incremental migration approaches where possible: migrate historical data before cutover, use change data capture for incremental changes, run新旧 systems in parallel with application-level routing, and design for minimal cutover window. The goal is reducing the cutover window to the minimum necessary for final sync and validation. Complex migrations often require some downtime, but careful planning can minimize it significantly.

---

Conclusion

Legacy database migration is high-risk work that rewards careful planning, thorough testing, and humble acknowledgment of what you do not know. The technical challenges are significant—schema conversion, SQL modernization, data integrity—but the biggest risks often come from undocumented business logic and dependencies that only reveal themselves when migration is underway.

AI assists migration by helping analyze legacy code, generate conversion scripts, document business logic, and create validation checks. But AI cannot reverse-engineer undocumented requirements, cannot guarantee that behavior is preserved, and cannot replace thorough testing. Use AI to accelerate the work while maintaining the rigor that prevents data loss and ensures migration success.

The prompts in this guide help data engineers plan migrations, convert schemas, modernize SQL, migrate data safely, validate results, and plan for rollback. Use these prompts to assess your legacy database, identify risks, and execute migrations that preserve data integrity while enabling modernization.

The goal is migration success—data that arrives correctly, applications that work as expected, and business that continues without interruption. That outcome requires thorough planning, careful execution, and validation that proves migration success before you declare it complete.

Key Takeaways:

1. Data integrity is paramount—verify everything, trust nothing.

2. Undocumented logic is the biggest risk—understand before migrating.

3. Incremental migration reduces risk—avoid big bang cutovers.

4. Rollback capability is essential—hope for success, plan for failure.

5. Validation requires multiple approaches—row counts, checksums, application tests.

Migration success comes from thorough preparation, not optimistic assumptions.