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

1. 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

1. Select the database from the dropdown at the top:

• Type: TestDB (if dropdown is empty)

1. 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

1. Choose Destination:

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

1. Select Source Tables:

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

1. 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

\