How to Transfer Data Between Two SQL Servers?

What Moves When You Transfer Data Between Two SQL Servers?

Four layers of data move in a safe transfer. Most methods move only the first two layers, which are database objects and rows:

• Database Objects. Tables, views, stored procedures, triggers, indexes and keys sit inside the database.
• Rows. Table data moves with the objects in a backup and alone in the data copy tools.
• Logins. Server logins live in the master database and a backup of a user database does not include them.
• SQL Server Agent Tasks. Scheduled tasks live in the msdb database and do not move with a user database.

Which Checks Come Before You Transfer Data Between Two SQL Servers?

Six checks come before any transfer:

1. Take a full backup of the source database. Scripts for every backup type sit in Explore Different Types of Backups in SQL Server.
2. Confirm that the destination SQL Server release matches the source or is newer.
3. Check the free space on the data volume and the log volume of the destination.
4. Confirm your rights. Copy Database Wizard users need the sysadmin role on both servers and import wizard users need SELECT on the source tables and INSERT on the destination tables.
5. Note the collation of the source database.
6. List the logins and the Agent tasks that the application needs.

6 Ways to Transfer Data Between SQL Servers

Five methods use tools from Microsoft and the sixth adds software for live and offline sources.

Method #1: Backup and Restore (Free, Moves One Database)

Backup and restore can transfer data between two SQL servers as one full database with its objects and rows. Microsoft names the BACKUP and RESTORE statements for copying an entire database. Six steps copy SalesDB to a new server:

1. Connect to the old server in SSMS and click New Query.
2. Back up SalesDB with this script:

BACKUP DATABASE SalesDB
TO DISK = N'D:\Backup\SalesDB.bak'
WITH CHECKSUM, INIT;

3. Copy SalesDB.bak to a folder that the new server can open.
4. Open the new server and list the logical file names:

RESTORE FILELISTONLY
FROM DISK = N'E:\Transfer\SalesDB.bak';

5. Restore the database and relocate every file with MOVE:

RESTORE DATABASE SalesDB
FROM DISK = N'E:\Transfer\SalesDB.bak'
WITH MOVE N'SalesDB' TO N'E:\Data\SalesDB.mdf',
     MOVE N'SalesDB_log' TO N'F:\Logs\SalesDB_log.ldf',
     RECOVERY;

6. Refresh Databases in Object Explorer to see SalesDB.

Note: Logins do not follow the backup. Users inside SalesDB then show as orphaned and ALTER USER AppUser WITH LOGIN = AppUser links a user to the login of the same name on the new server.

Method #2: Open Copy Database Wizard (Free, Moves Logins Too)

Copy Database Wizard copies or moves a database between servers and optionally copies logins and Agent tasks. Three conditions apply before you start:

• SQL Server Agent is active on the destination server.
• Your account belongs to the sysadmin role on both servers.
• Your edition is not Express.

Ten steps copy SalesDB:

1. Open SSMS and connect to the source server.
2. Expand Databases and right click SalesDB.
3. Select Tasks and then Copy Database.
4. Confirm the source server and authentication on Select a Source Server and click Next.
5. Enter the destination server on Select a Destination Server and click Next.
6. Select Use the detach and attach method for speed. Select Use the SQL Management Object method when users still need the database.
7. Select Copy on the database list and click Next.
8. Confirm the new database name and file paths on Configure Destination Database.
9. Add the logins and Agent tasks that the application needs on Select Server Objects.
10. Click Next until the wizard shows Finish and then click Finish.

    Microsoft states that Move deletes the source database after the transfer. Select Copy until you verify the new server.

Per the Copy Database Wizard article the detach and attach method makes the database unavailable during the transfer. SMO is slower because it recreates every object and users can still open the database.

Method #3: Use the Import and Export Wizard (Free, Moves Rows)

Import wizard copies rows from chosen tables and views to another server. Use it to transfer data between two SQL servers when you need only a few tables. Microsoft explains in the Import and Export Wizard overview that the wizard uses SQL Server Integration Services to copy data and that you can save the package when SQL Server Standard Edition or higher is installed. Eight steps copy two tables:

1. Right click the destination database in Object Explorer.
2. Select Tasks and then Import Data.
3. Select a SQL Server driver in Data source on Choose a Data Source and enter the old server, authentication and database.
4. Enter the new server details on Choose a Destination.
5. Select Copy data from one or more tables or views on Specify Table Copy or Query.
6. Tick the tables on Select Source Tables and Views.
7. Click Edit Mappings and tick Enable identity insert for a table with an identity column.
8. Select Run immediately and click Finish.

Note: The wizard focuses on rows. Script the tables first when the destination needs keys, indexes and constraints.

Method #4: Query a Linked Server (Free, Moves Rows With T-SQL)

Linked servers query the tables of another server through a four part name. Microsoft shows the name as linked_server.catalog.schema.object in its linked servers article. Five steps copy a table:

1. Connect to the destination and click New Query.
2. Add the old server as a linked server:

EXEC sp_addlinkedserver
     @server = N'OLDSQL',
     @srvproduct = N'SQL Server';

3. Map your current login to the linked server:

EXEC sp_addlinkedsrvlogin
     @rmtsrvname = N'OLDSQL',
     @useself = N'True';

4. Insert the rows into a table that exists on the new server:

INSERT INTO SalesDB.dbo.Customers (CustomerID, Name, City)
SELECT CustomerID, Name, City
FROM OLDSQL.SalesDB.dbo.Customers;

5. Create a new table from the remote rows when no table exists:

SELECT *
INTO SalesDB.dbo.Orders_Copy
FROM OLDSQL.SalesDB.dbo.Orders;

Note: Use SET IDENTITY_INSERT dbo.Customers ON before an INSERT that supplies identity values. SELECT INTO builds a table from the result set and you add keys and indexes afterward. SQL Server 2019 and later use the Microsoft OLE DB Driver for SQL Server as the default provider according to Microsoft.

Method #5: Use the BCP Utility (Free, Moves Rows in Bulk)

bcp exports a table to a data file and imports the file into another table. Microsoft states in the bcp utility reference that bcp copies data only and the destination table must exist. Five steps move the Customers table:

1. Open Command Prompt on a computer that reaches both servers.
2. Export the table in native format with a trusted connection:

bcp SalesDB.dbo.Customers out C:\Transfer\Customers.dat -n -S OLDSQL -T

3. Create the Customers table on the destination with a script.
4. Import the data file:

bcp SalesDB.dbo.Customers in C:\Transfer\Customers.dat -n -S NEWSQL -T

5. Compare the row counts of both tables.

Note: Microsoft recommends the -n native format for transfers between SQL Server instances. Export needs SELECT on the table and import needs SELECT and INSERT.

Method #6: Migrate a Live Database With EmailDoctor (Free Preview, Paid Export)

Some transfers involve a damaged file or a server that is offline. EmailDoctor SQL Server Data Migration Tool connects to a live server or opens an MDF file and sends the objects to a database on another server.

The software limits the free trial to 25 records in every table and five other categories. Buying a license removes the limitation.

Five options shape the export:

• Destination. Create a new database or export to an existing one.
• Objects. Select tables, views, stored procedures, triggers and other objects.
• Content. Export the schema only or the schema with data.
• Collation. Set the collation settings for the new database.
• Report. Save a migration report in CSV format.

Add the Live Database and Preview Objects

Seven steps add the database and show its objects:

1. Install the migration software on a Windows PC that reaches both servers and click Open.
2. Select Online mode.
3. Select Live SQL Server with Authentication and enter the details of the old server.
4. Select the database in the Drop Down List and click OK.
5. Click Close when the scan ends.
6. Open the database tree on the left side.
7. Select a table, a view or a procedure to preview it.

Export to the New Server

Ten steps send the objects to the new server:

8. Click Export at the top.
9. Select SQL Server Database as the migration type.
10. Enter the Server Name with Authentication details of the new server.
11. Select Create New Database for a fresh copy or Export to Existing Database to fill a database that exists.
12. Set Collation Settings when your application needs them.
13. Select the database objects to export.
14. Select With only Schema or With Schema & Data.
15. Click Save.
16. Click OK on the Export Completed message.
17. Select a Location and click OK to save the report.

Note: Offline mode accepts an MDF file through Browse and scans it with Quick Scan or Advance Scan. Select SQL Server Compatible SQL Scripts as the migration type to create a script file instead of a live copy.

How to Check That the Transfer Worked?

Compare row counts, test the database for errors and fix orphaned users on the destination. Three checks confirm the transfer:

1. Execute this query on both servers and compare the numbers:

SELECT t.name, SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.partitions AS p
  ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY t.name;

2. Test the new database for damage. No output means no errors:

DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS;

3. List users without a login on the new server:

SELECT dp.name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U') AND sp.sid IS NULL AND dp.principal_id > 4;

Which Problems Stop a SQL Server Data Transfer?

Five problems stop a transfer of data between two SQL servers:

Frequently Asked Questions

Author’s Verdict

Backup and restore is the safest choice for one database because one file moves in a single step. Server logins and scheduled tasks need their own attention.

Copy Database Wizard adds those server objects but needs the sysadmin role and an edition above Express. Data copy tools fit single tables and repeat copies.

Compare row counts before you retire the old server. Migration software handles damaged files and offline sources when no backup is readable.