Script Generator for Database Structure and Data Transfer
🔍 About the Project
This project helps with transferring the structure and data between two databases of the same type — either SQL Server to SQL Server or Oracle to Oracle. It is useful when you need to synchronize two environments, such as development and production, or prepare a clean testing environment.
The main goal was to automatically create scripts for database objects and data, based on defined rules and object dependencies.
⚙️ Technologies Used
SQL Server Side:
- T-SQL for core logic
- PowerShell to save scripts in files
- SMO (SQL Server Management Objects) to generate object scripts
Oracle Side:
- PL/SQL for core logic
- PowerShell for file generation
- Oracle built-in functions to extract object definitions
🧠 Challenges Solved
- Building a dependency graph between objects (tables, views, packages, triggers, etc.)
- Managing complex object relations and schema matching
- Supporting large data types like CLOB and BLOB in Oracle
- Respecting foreign key constraints and object order
- Handling data transfer for large tables using batching
🔄 Process Steps
- Preparation Phase
Create helper tables and configuration tables needed for execution. - Dependency Analysis
Find object dependencies using system views, CTEs, and cursors. - Graph Building
Organize objects into levels: start from independent ones and go to the most dependent. - Object Processing
- If the object is missing in the target: generate a CREATE script.
- If it exists but is different: generate an ALTER script.
- If data needs to be copied: generate INSERT scripts for each row.
- Constraint Handling
Disable foreign key constraints temporarily when inserting data, then re-enable them. - Oracle-Specific Needs
In Oracle, packages had to be moved first. Triggers and sequences were also handled carefully.
📥 Script Generation Details
- SQL Server: SMO was very helpful but a bit slow for large tables.
- Oracle: Built-in functions were faster but less flexible.
- Large Tables: Data scripts were created in small batches to avoid size and performance issues.
🎯 Project Use Case
This project is especially helpful in:
- Database synchronization between dev and prod
- Versioning and deployment
- Environment setup for testing or training
The final output is a semi-automated tool that creates all needed scripts based on the database content and structure.
🚀 Results
The project improved speed and accuracy in managing database deployments. It also reduced the manual work needed for comparing and copying databases. It is flexible enough to grow and support more features like data comparison or security rules in future versions.
More info
You can find more info in my GitHub.