Database migration is a common task when moving to a new server, creating backups, or setting up development/staging environments. In this guide, we’ll walk through the complete process of migrating a database from a source server to a destination server, including both the database structure and data. Use Case We have: Source Server: Contains database TestDB with a cities table
Destination Server: Empty server where we need to create a clone of TestDB
Goal: Migrate database schema and copy all data to the destination Source Server: Contains database TestDB with a cities table Source Server: TestDB cities Destination Server: Empty server where we need to create a clone of TestDB Destination Server: TestDB Goal: Migrate database schema and copy all data to the destination Goal: Prerequisites SQL Server Management Studio (SSMS) installed
Access credentials for both source and destination servers
Appropriate permissions (CREATE DATABASE, db_owner) SQL Server Management Studio (SSMS) installed Access credentials for both source and destination servers Appropriate permissions (CREATE DATABASE, db_owner) Part 1: Setting Up Sample Data (Source Server) First, let’s create our sample database on the source server: -- Connect to Source Server in SSMS
-- Create TestDB database
CREATE DATABASE TestDB;
GO

USE TestDB;
GO

-- Create cities table
CREATE TABLE cities (
    city_id INT IDENTITY(1,1) PRIMARY KEY,
    city_name NVARCHAR(100) NOT NULL,
    country NVARCHAR(100) NOT NULL,
    population INT,
    established_year INT
);
GO

-- Insert sample data (5 cities)
INSERT INTO cities (city_name, country, population, established_year) VALUES
('New York', 'United States', 8336817, 1624),
('London', 'United Kingdom', 9002488, 43),
('Tokyo', 'Japan', 13960000, 1603),
('Dubai', 'United Arab Emirates', 3331420, 1833),
('Sydney', 'Australia', 5312163, 1788);
GO

-- Verify data
SELECT * FROM cities; -- Connect to Source Server in SSMS
-- Create TestDB database
CREATE DATABASE TestDB;
GO

USE TestDB;
GO

-- Create cities table
CREATE TABLE cities (
    city_id INT IDENTITY(1,1) PRIMARY KEY,
    city_name NVARCHAR(100) NOT NULL,
    country NVARCHAR(100) NOT NULL,
    population INT,
    established_year INT
);
GO

-- Insert sample data (5 cities)
INSERT INTO cities (city_name, country, population, established_year) VALUES
('New York', 'United States', 8336817, 1624),
('London', 'United Kingdom', 9002488, 43),
('Tokyo', 'Japan', 13960000, 1603),
('Dubai', 'United Arab Emirates', 3331420, 1833),
('Sydney', 'Australia', 5312163, 1788);
GO

-- Verify data
SELECT * FROM cities; Part 2: Generate Database Migration Script Now let’s create a script to migrate the database structure to the destination server. Step 1: Open Generate Scripts Wizard In SSMS, connect to your Source Server
In Object Explorer (left panel), locate your database
Right-click on TestDB database
Select Tasks → Generate Scripts… In SSMS, connect to your Source Server Source Server In Object Explorer (left panel), locate your database Object Explorer Right-click on TestDB database Right-click TestDB Select Tasks → Generate Scripts… Tasks → Generate Scripts… Step 2: Choose Objects to Script Click Next on the Introduction screen
Select “Select specific database objects”
Expand Tables and check:

dbo.cities


Click Next Click Next on the Introduction screen Next Select “Select specific database objects” “Select specific database objects” Expand Tables and check:

dbo.cities Tables dbo.cities dbo.cities dbo.cities Click Next Next Step 3: Set Scripting Options Click Next to reach “Set Scripting Options”
Choose “Save to file”
Click Browse and save as: C:\Temp\TestDB_Schema.sql
Click the Advanced button
Find “Types of data to script” and select “Schema only”
Click OK
Click Next → Finish Click Next to reach “Set Scripting Options” Next Choose “Save to file” “Save to file” Click Browse and save as: C:\Temp\TestDB_Schema.sql Browse C:\Temp\TestDB_Schema.sql Click the Advanced button Advanced Find “Types of data to script” and select “Schema only” “Types of data to script” “Schema only” Click OK OK Click Next → Finish Next Finish The wizard will generate a SQL script file containing all CREATE statements for your database objects. Part 3: Create Database on Destination Server Option A: Create Database Manually (Recommended for Different Servers) Connect to your Destination Server in SSMS and run: Destination Server -- Create the database on destination server
CREATE DATABASE TestDB;
GO

USE TestDB;
GO -- Create the database on destination server
CREATE DATABASE TestDB;
GO

USE TestDB;
GO Option B: Include Database Creation in Script If you want the script to create the database automatically: Follow the same Generate Scripts steps
But in Step 2, select “Script entire database and all database objects”
The script will include CREATE DATABASE statements Follow the same Generate Scripts steps But in Step 2, select “Script entire database and all database objects” “Script entire database and all database objects” The script will include CREATE DATABASE statements CREATE DATABASE Part 4: Execute Migration Script on Destination Connect to Destination Server in SSMS
Open the generated script:

File → Open → File
Select C:\Temp\TestDB_Schema.sql


Select the database from the dropdown at the top:

Type: TestDB (if dropdown is empty)


Execute the script by pressing F5 Connect to Destination Server in SSMS Connect to Destination Server Open the generated script:

File → Open → File
Select C:\Temp\TestDB_Schema.sql Open the generated script: File → Open → File
Select C:\Temp\TestDB_Schema.sql File → Open → File Select C:\Temp\TestDB_Schema.sql C:\Temp\TestDB_Schema.sql Select the database from the dropdown at the top:

Type: TestDB (if dropdown is empty) Select the database Type: TestDB (if dropdown is empty) Type: TestDB (if dropdown is empty) TestDB Execute the script by pressing F5 Execute the script F5 Your database structure is now created on the destination server! Verify Structure Creation -- Run on Destination Server
USE TestDB;
GO

-- Check if table exists
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'cities';

-- Check table structure
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'cities'; -- Run on Destination Server
USE TestDB;
GO

-- Check if table exists
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'cities';

-- Check table structure
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'cities'; Part 5: Copy Data from Source to Destination Now that we have the database structure in place, let’s copy the data. Method 1: Using Generate Scripts (Recommended for Small Datasets) Step 1: Generate Data Script Connect to Source Server in SSMS
Right-click TestDB → Tasks → Generate Scripts
Select the cities table
Click Next → Advanced
Change “Types of data to script” to “Data only”
Save to file: C:\Temp\TestDB_Data.sql
Click Next → Finish Connect to Source Server in SSMS Source Server Right-click TestDB → Tasks → Generate Scripts TestDB Tasks → Generate Scripts Select the cities table cities Click Next → Advanced Next Advanced Change “Types of data to script” to “Data only” “Types of data to script” “Data only” Save to file: C:\Temp\TestDB_Data.sql C:\Temp\TestDB_Data.sql Click Next → Finish Next Finish Step 2: Execute Data Script on Destination Connect to Destination Server
Select database: TestDB
Open the data script file
Execute (F5) Connect to Destination Server Destination Server Select database: TestDB TestDB Open the data script file Execute (F5) -- The generated script will look something like this:
INSERT INTO [dbo].[cities] (city_name, country, population, established_year) 
VALUES 
('New York', 'United States', 8336817, 1624),
('London', 'United Kingdom', 9002488, 43),
('Tokyo', 'Japan', 13960000, 1603),
('Dubai', 'United Arab Emirates', 3331420, 1833),
('Sydney', 'Australia', 5312163, 1788); -- The generated script will look something like this:
INSERT INTO [dbo].[cities] (city_name, country, population, established_year) 
VALUES 
('New York', 'United States', 8336817, 1624),
('London', 'United Kingdom', 9002488, 43),
('Tokyo', 'Japan', 13960000, 1603),
('Dubai', 'United Arab Emirates', 3331420, 1833),
('Sydney', 'Australia', 5312163, 1788); Method 2: Using Import/Export Wizard (Good for Larger Datasets) Connect to Source Server in SSMS
Right-click TestDB → Tasks → Export Data
Choose Data Source:

Data Source: Microsoft OLE DB Driver for SQL Server
Server name: Your source server
Database: TestDB
Click Next


Choose Destination:

Destination: Microsoft OLE DB Driver for SQL Server
Server name: Your destination server
Database: TestDB
Click Next


Select Source Tables:

Select “Copy data from one or more tables or views”
Check: [dbo].[cities]
Click Next


Run Immediately → Finish Connect to Source Server in SSMS Source Server Right-click TestDB → Tasks → Export Data TestDB Tasks → Export Data Choose Data Source:

Data Source: Microsoft OLE DB Driver for SQL Server
Server name: Your source server
Database: TestDB
Click Next Choose Data Source: Data Source: Microsoft OLE DB Driver for SQL Server
Server name: Your source server
Database: TestDB
Click Next Data Source: Microsoft OLE DB Driver for SQL Server Microsoft OLE DB Driver for SQL Server Server name: Your source server Database: TestDB TestDB Click Next Next Choose Destination:

Destination: Microsoft OLE DB Driver for SQL Server
Server name: Your destination server
Database: TestDB
Click Next Choose Destination: Destination: Microsoft OLE DB Driver for SQL Server
Server name: Your destination server
Database: TestDB
Click Next Destination: Microsoft OLE DB Driver for SQL Server Microsoft OLE DB Driver for SQL Server Server name: Your destination server Database: TestDB TestDB Click Next Next Select Source Tables:

Select “Copy data from one or more tables or views”
Check: [dbo].[cities]
Click Next Select Source Tables: Select “Copy data from one or more tables or views”
Check: [dbo].[cities]
Click Next Select “Copy data from one or more tables or views” “Copy data from one or more tables or views” Check: [dbo].[cities] [dbo].[cities] Click Next Next Run Immediately → Finish Run Immediately Finish The wizard will copy all data directly from source to destination.Summary of Steps Database Migration: Generate schema script from source database
Create database on destination server
Execute schema script on destination Generate schema script from source database Create database on destination server Execute schema script on destination Data Migration: Generate data script from source tables
Execute data script on destination Generate data script from source tables Execute data script on destination

This story contains AI-generated text. The author has used AI either for research, to generate outlines, or write the text itself. 

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

How to Migrate an SQL Server Database: A Complete Step-by-Step Guide

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Docker Environment Variables: Development vs Production Made Simple

6 Database Migration Tools For Complete Data Integrity & More

An Easy Guide to Learn EF Core Migrations

An In-depth Look Into MySQL Vs. PostgreSQL

Business Logic Migration Made Faster Than Your Coffee Brews

Deleting a Column from a Django Model on Production

Docker Environment Variables: Development vs Production Made Simple

6 Database Migration Tools For Complete Data Integrity & More

An Easy Guide to Learn EF Core Migrations

An In-depth Look Into MySQL Vs. PostgreSQL

Business Logic Migration Made Faster Than Your Coffee Brews

Deleting a Column from a Django Model on Production

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps