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

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

Prerequisites

• 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;

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

1. In SSMS, connect to your Source Server
2. In Object Explorer (left panel), locate your database
3. Right-click on TestDB database
4. Select Tasks → Generate Scripts…

Step 2: Choose Objects to Script

1. Click Next on the Introduction screen
2. Select “Select specific database objects”
3. Expand Tables and check:
   • dbo.cities
4. Click Next

Step 3: Set Scripting Options

1. Click Next to reach “Set Scripting Options”
2. Choose “Save to file”
3. Click Browse and save as: C:\Temp\TestDB_Schema.sql
4. Click the Advanced button
5. Find “Types of data to script” and select “Schema only”
6. Click OK
7. Click 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:

-- 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:

1. Follow the same Generate Scripts steps
2. But in Step 2, select “Script entire database and all database objects”
3. The script will include CREATE DATABASE statements

Part 4: Execute Migration Script on Destination

1. Connect to Destination Server in SSMS
2. Open the generated script:
   • File → Open → File
   • Select C:\Temp\TestDB_Schema.sql
3. Select the database from the dropdown at the top:
   • Type: TestDB (if dropdown is empty)
4. Execute the script by pressing 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';

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

1. Connect to Source Server in SSMS
2. Right-click TestDB → Tasks → Generate Scripts
3. Select the cities table
4. Click Next → Advanced
5. Change “Types of data to script” to “Data only”
6. Save to file: C:\Temp\TestDB_Data.sql
7. Click Next → Finish

Step 2: Execute Data Script on Destination

1. Connect to Destination Server
2. Select database: TestDB
3. Open the data script file
4. 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);

Method 2: Using Import/Export Wizard (Good for Larger Datasets)

1. Connect to Source Server in SSMS
2. Right-click TestDB → Tasks → Export Data
3. Choose Data Source:
   • Data Source: Microsoft OLE DB Driver for SQL Server
   • Server name: Your source server
   • Database: TestDB
   • Click Next
4. Choose Destination:
   • Destination: Microsoft OLE DB Driver for SQL Server
   • Server name: Your destination server
   • Database: TestDB
   • Click Next
5. Select Source Tables:
   • Select “Copy data from one or more tables or views”
   • Check: [dbo].[cities]
   • Click Next
6. Run Immediately → Finish

The wizard will copy all data directly from source to destination.Summary of Steps

Database Migration:

1. Generate schema script from source database
2. Create database on destination server
3. Execute schema script on destination

Data Migration:

1. Generate data script from source tables
2. Execute data script on destination