Buy Crypto Markets Spot FuturesGOLD Earn Event Center

This guide walks through migrating a SQL Server database from a source server to a destination server, covering schema scripting, database creation, and data transfer using both Generate Scripts and the Import/Export Wizard.vThis guide walks through migrating a SQL Server database from a source server to a destination server, covering schema scripting, database creation, and data transfer using both Generate Scripts and the Import/Export Wizard.v

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

Author: Hackernoon

Source: Hackernoon

2025/12/08 22:06

4 min read

For feedback or concerns regarding this content, please contact us at crypto.news@mexc.com

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

In SSMS, connect to your Source Server
In Object Explorer (left panel), locate your database
Right-click on TestDB database
Select 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

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

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:

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

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

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

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

Step 2: Execute Data Script on Destination

Connect to Destination Server
Select database: 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);

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

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

Data Migration:

Generate data script from source tables
Execute data script on destination

Disclaimer: The articles reposted on this site are sourced from public platforms and are provided for informational purposes only. They do not necessarily reflect the views of MEXC. All rights remain with the original authors. If you believe any content infringes on third-party rights, please contact crypto.news@mexc.com for removal. MEXC makes no guarantees regarding the accuracy, completeness, or timeliness of the content and is not responsible for any actions taken based on the information provided. The content does not constitute financial, legal, or other professional advice, nor should it be considered a recommendation or endorsement by MEXC.

Tags:

#Options