How to generate data migration scripts with Microsoft Copilot: step by step

This tutorial shows how to use Microsoft Copilot to create data migration scripts between databases, with field mapping, simple transformations and validations. Knowing how to generate these scripts with Copilot speeds up migrations, reduces manual errors and provides an auditable starting point for developers and DBAs.

Prerequisites

• Account with access to Microsoft Copilot (Copilot for Microsoft 365 or Copilot Studio depending on availability).
• Access to both databases (source and target) and a simple schema — e.g.: SQL Server, PostgreSQL or Azure SQL.
• Code editor (VS Code recommended) and SQL client to test scripts.
• Basic knowledge of SQL and ETL mapping (extract, transform, load).

Step 1: Prepare the migration description

Explain to Copilot, clearly and structured, what you intend to migrate: tables, columns, types, transformation rules and approximate volume. The quality of the description determines the usefulness of the generated script.

Example description:
Migrate the Customers table from SQLServer to Azure SQL.
Columns:
- CustomerID int -> CustomerId int (PK)
- FirstName varchar(100) + LastName varchar(100) -> FullName nvarchar(200) (concat with space)
- Email varchar(200) -> Email nvarchar(200) (lowercase)
- CreatedDate datetime -> CreatedUtc datetime2 (convert to UTC)
Rules:
- Ignore records with Email NULL
- Batches of 1000 records per transaction
- Error log to a MigrationErrors table

Step 2: Ask Copilot for the first version of the script

In the editor where you have Microsoft Copilot active, paste the description and request a SQL script that performs the migration in batches, with transactions and inserts to the target database. Also ask for the creation of the MigrationErrors table.

Example prompt for Copilot:
"Generate a T-SQL script that migrates the Customers table following the Description above. Include: creation of MigrationErrors, loop by batches of 1000, transactions, handling of NULL emails and date/name conversions. Provide comments in the code."

Step 3: Review and adjust the script

Copilot returns a base script. Read carefully: verify transactions, data types, conversions and WHERE clauses. Adjust schema/table names and indexes according to your infrastructure.

Example output (simplified):
BEGIN TRANSACTION;
-- Create errors table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MigrationErrors')
CREATE TABLE MigrationErrors (ErrorId INT IDENTITY(1,1), CustomerID INT, ErrorMsg NVARCHAR(4000), CreatedUtc DATETIME2 DEFAULT SYSUTCDATETIME());

DECLARE @BatchSize INT = 1000, @MinId INT = 0, @MaxId INT;
SELECT @MaxId = MAX(CustomerID) FROM SourceDB.dbo.Customers;
WHILE @MinId < ISNULL(@MaxId,0)
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO TargetDB.dbo.Customers (CustomerId, FullName, Email, CreatedUtc)
    SELECT CustomerID,
           RTRIM(FirstName) + ' ' + RTRIM(LastName) AS FullName,
           LOWER(Email) AS Email,
           SWITCHOFFSET(CONVERT(datetimeoffset, CreatedDate), '+00:00') AS CreatedUtc
    FROM SourceDB.dbo.Customers
    WHERE Email IS NOT NULL AND CustomerID > @MinId
    ORDER BY CustomerID
    OFFSET 0 ROWS FETCH NEXT @BatchSize ROWS ONLY;

    SET @MinId = @MinId + @BatchSize;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    INSERT INTO MigrationErrors (CustomerID, ErrorMsg)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
    ROLLBACK TRANSACTION;
    BREAK;
  END CATCH
END

Step 4: Test in a development environment

Run the script first on a small copy of the database. Verify that the transformations (concat, lowercase, timezone) are correct and that the MigrationErrors table records issues. Adjust the BatchSize for performance.

Step 5: Add post-migration validations

Ask Copilot to generate validation queries: counts per table, simple checksums and sample comparisons between source and target. These queries help confirm integrity.

Example prompt:
"Generate queries to validate migration: 1) total record count per table; 2) difference of NULL emails; 3) simple checksum per batch using HASHBYTES."

Step 6: Automate safe execution

Use a script file or a job (e.g.: SQL Agent, Azure Data Factory for cloud versions) to run with logs and alerts. Ask Copilot for an example job or simple pipeline if you need a template.

Example command for SQL Agent (conceptual):
-- Create job that calls migration stored procedure
EXEC msdb.dbo.sp_add_job @job_name = 'Job_Migrate_Customers';
-- add steps and schedule as needed

Verify the result

Confirm that the number of records in the target table matches the expected amount, that no NULL emails were transferred, and that the MigrationErrors table is empty (or only contains expected records). Run the generated validation queries and analyze transaction logs and time per batch for performance.

Conclusion

Using Microsoft Copilot to generate migration scripts speeds up developing the base script and saves time on repetitive tasks. Next steps: create automated tests, tune performance with indexes and plan rollbacks. Tip: always review manually and test in an isolated environment before production — what is the most critical transformation in your migration?