Migrate databases to SQL Server

dbForge Studio for SQL Server provides several tools for database migration. This section describes the supported approaches that use Data Export/Import, AI Assistant, Schema Compare, and Data Compare.

Warning

Back up your database before you start the migration to prevent data loss.

Migrate a database to a newer version of SQL Server

To upgrade a database to the latest version of SQL Server and use its latest features, use dbForge Schema Compare and Data Compare.

These tools enable you to compare database objects and data, and migrate only the items you need.

Alternatively, you can generate a migration script that includes the required objects and data, and then run it on the target SQL Server instance.

For detailed instructions, see Overcoming the challenges.

Migrate databases between SQL Server instances

Moving databases between servers typically means copying a database from a source server and deploying it on a target server:

• Back up and restore.

• Detach and attach.

• Synchronize data using Data and Schema Compare tools.

• Generate a database script, script folder, or schema snapshot, then run it on a target server.

For more information on how to use each method, see Different methods to copy data with dbForge SQL tools.

Migrate a database using ODBC

To migrate a database using an ODBC driver, connect the source database (for example, MySQL) to SQL Server through the ODBC interface. You can then transfer both schema and data by using the SQL Server Data Import and Export Wizards.

For more information, see Convert MySQL databases to SQL Server.

Migrate an external database using the AI Assistant

The integrated AI Assistant can help you transfer data from external database systems to SQL Server. It analyzes the script generated for the source database and converts it to SQL Server–compatible syntax. You can then run the resulting script in dbForge Studio for SQL Server against the target database.

Prepare data migration

Before migrating data, you need to export the source database schema and data to keep the target system consistent.

1. Export a database schema. For instructions, see Export schema.

2. Export the database data. For instructions, see Export data.

Migrate the database

Data migration from another database system involves converting the schema script into SQL Server–compatible syntax, applying it to the target database, and then importing the data to complete the migration.

Step 1: Convert the script to SQL Server syntax

1. On the toolbar, select AI Assistant > Open Chat Window.

2. In the AI Assistant window, enter a prompt to convert the schema script from the source database system (specify the version) to SQL Server (specify the version).

Tips

For large external database scripts, split them into smaller parts and send each segment to the AI Assistant for conversion.

3. Select Send.

AI Assistant generates a SQL Server-compatible script.

Click to open the generated SQL-compatible schema script

 -- Use the target database
USE salesdb;
GO

-- Drop tables if exist in correct order due to FKs
IF OBJECT_ID('dbo.orderitems', 'U') IS NOT NULL DROP TABLE dbo.orderitems;
IF OBJECT_ID('dbo.products', 'U') IS NOT NULL DROP TABLE dbo.products;
IF OBJECT_ID('dbo.orders', 'U') IS NOT NULL DROP TABLE dbo.orders;
IF OBJECT_ID('dbo.customers', 'U') IS NOT NULL DROP TABLE dbo.customers;
GO

-- Create table customers
CREATE TABLE dbo.customers (
  CustomerID int NOT NULL PRIMARY KEY,
  Name nvarchar(100) NULL,
  Email nvarchar(100) NULL,
  Phone nvarchar(20) NULL,
  Address nvarchar(150) NULL,
  City nvarchar(50) NULL
);
GO

-- Create table orders
CREATE TABLE dbo.orders (
  OrderID int NOT NULL PRIMARY KEY,
  CustomerID int NULL,
  OrderDate date NULL,
  TotalAmount decimal(10, 2) NULL,
  Status nvarchar(50) NULL,
  ShippingMethod nvarchar(50) NULL,
  CONSTRAINT FK_orders_customers FOREIGN KEY (CustomerID) REFERENCES dbo.customers(CustomerID)
);
GO

-- Create table products
CREATE TABLE dbo.products (
  ProductID int NOT NULL PRIMARY KEY,
  Name nvarchar(100) NULL,
  Category nvarchar(50) NULL,
  Price decimal(10, 2) NULL,
  StockQty int NULL,
  Supplier nvarchar(100) NULL
);
GO

-- Create table orderitems
CREATE TABLE dbo.orderitems (
  OrderItemID int NOT NULL PRIMARY KEY,
  OrderID int NULL,
  ProductID int NULL,
  Quantity int NULL,
  UnitPrice decimal(10, 2) NULL,
  SubTotal decimal(10, 2) NULL,
  CONSTRAINT FK_orderitems_orders FOREIGN KEY (OrderID) REFERENCES dbo.orders(OrderID),
  CONSTRAINT FK_orderitems_products FOREIGN KEY (ProductID) REFERENCES dbo.products(ProductID)
);
GO

-- Set database default collation if needed externally (not within table creation)
-- UTF8 requires SQL Server 2019+ and specifying COLLATE Latin1_General_100_CI_AS_SC_UTF8 on VARCHAR columns for Unicode UTF-8 storage.
-- Here we use NVARCHAR for Unicode storage.
 

Step 2: Execute the script

1. On the toolbar, select New SQL to open a new SQL document.

2. Create the database for the script.

CREATE DATABASE your_database_name;

3. Paste the script generated by AI Assistant into the SQL document.

4. Click Execute.

Step 3: Import database data

Import the source database content from the exported data file. For instructions, see Import data.

Verify data integrity after migration

After migration, verify that the source and target databases contain the same data. Export data from both systems in the same format (for example, CSV) and compare the exported files in a diff tool such as Code Compare.

1. Export data from the target database. For instructions, see Export data.

2. Compare the source and target data.

2.1. Open Code Compare.

2.2. On the menu bar, select File > Open First File.

2.3. Select the CSV file you exported from the source database.

2.4. Select File > Open Second File.

2.5. Select the CSV file you exported from the target database.

2.6. Review the side-by-side comparison to confirm that no differences exist.

Recommendations

Follow these recommendations to ensure an effective database migration.