How to Copy SQL Database From One Server to Another?

What Does a Database Copy Include?

Database copies include the data file and the log with every object stored inside them. Tables, views, stored procedures, users and permissions travel with the files while server logins and Agent tasks remain outside.

Which Method can Copy a SQL Database From One Server to Another?

Select the method that matches your situation:

Four checks come before any copy:

1. Match the destination release to the source or choose a newer one.
2. Confirm free space on the destination for the data file and the log.
3. Note the collation and the owner of the database.
4. List the logins that the application uses.

5 Ways to Copy SQL Database to Another Server

Four methods rely on Microsoft tools and the fifth adds software for MDF files.

Method #1: Copy Only Backup and Restore (Free, Protects the Backup Chain)

Copy only backups copy the database without touching the regular backup sequence. Microsoft states in its copy only backups article that a copy only full backup does not affect the differential base. Six steps copy SalesDB:

1. Open the Tasks menu of SalesDB in Object Explorer and select Back Up.
2. Tick Copy-only backup in the Source section of the General page.
3. Add a file path under Destination and click OK.
4. Copy the backup file to a folder that the destination server can open.
5. Right click Databases at the destination and select Restore Database.
6. Select Device, add the backup file and click OK.

T-SQL needs one extra option for the same backup:

BACKUP DATABASE SalesDB
TO DISK = N'E:\BAK\SalesDB_Copy.bak'
WITH COPY_ONLY, CHECKSUM;

Method #2: Detach and Attach (Free, Source Goes Offline)

Detach takes the database out of the source instance and its data and log files remain in their folder. Copy those files and attach them on the destination server. Six conditions stop a detach according to Microsoft:

• Replicated and published databases.
• Databases with snapshots.
• Members of an Always On availability group.
• Databases in a mirroring session.
• Suspect databases.
• System databases.

Six steps move SalesDB:

1. Open the Tasks menu of SalesDB and select Detach.
2. Tick Drop Connections when users are connected and click OK.
3. Copy SalesDB.mdf and SalesDB_log.ldf to the destination server.
4. Right click Databases on the new instance and select Attach.
5. Click Add and select the MDF file.
6. Confirm the file paths and click OK.

T-SQL needs two statements for the same move:

EXEC sp_detach_db @dbname = N'SalesDB';

CREATE DATABASE SalesDB
ON (FILENAME = N'E:\Data\SalesDB.mdf'),
   (FILENAME = N'F:\Logs\SalesDB_log.ldf')
FOR ATTACH;

Note: Microsoft states that all data files must be available during the attach and that a database from a newer release cannot attach to an earlier one. Attach the files on the source again after the copy because detach removes the database from that instance.

Method #3: Copy Database Wizard (Free, Needs Sysadmin Rights)

Copy Database Wizard copies a database and optionally its logins and Agent tasks in one dialog. Microsoft lists three requirements on its Copy Database Wizard page:

• Your destination server has the Agent service started.
• Your login has sysadmin rights on both servers.
• Your edition is above Express.

Five steps start a copy:

1. Select SalesDB and choose Copy Database from Tasks.
2. Enter the destination server and authentication and click Next.
3. Select the transfer method and click Next.
4. Select the database and mark Copy to protect the source.
5. Add the logins you need under server objects and click Finish.

Note: The detach and attach method needs an SSIS proxy on the destination server. Microsoft states that without the proxy the process can fail and strip NTFS permissions from the source files. Details of both transfer methods sit in Transfer Data Between Two SQL Servers.

Method #4: Export and Import a BACPAC File (Free, One Portable File)

BACPAC files store the schema and the data of a database. Microsoft states in its import a BACPAC file article that the import creates a new database from the stored model and then bulk copies the data. Eight steps copy SalesDB:

1. Right click SalesDB and select Tasks and then Export Data-tier Application.
2. Select Save to local disk on Export Settings and type a path that ends in .bacpac.
3. Click Next and then Finish.
4. Copy the BACPAC file to the destination server.
5. Right click Databases and select Import Data-tier Application.
6. Select Import from local disk on Import Settings and browse to the file.
7. Type the New database name and the data and log file paths on Database Settings.
8. Click Next and then Finish.

SQL Server Authentication logins arrive disabled. Enable every login and set a new password:

ALTER LOGIN AppLogin ENABLE;
ALTER LOGIN AppLogin WITH PASSWORD = N'<new password>';

Note: According to Microsoft, the source server needs SQL Server 2005 SP4 or later. Databases with objects that a data tier application does not support cannot export.

Method #5: Copy a Database From an MDF File With EmailDoctor (Preview Free, Export Needs a License)

Some copies start with a loose MDF file and no working source server. EmailDoctor SQL Server Data Migration Tool opens the file and exports its objects as a database on a different server.

Work on a duplicate so the original MDF file remains untouched and copy it to a separate folder first. EmailDoctor software sets two trial limits at 25 records per table and five other categories. Licensed versions export without that cap.

Open the MDF File and Preview Objects

Nine steps open the file and show its objects:

1. Launch the software on your Windows PC and choose Offline mode.
2. Click Browse and add the file.
3. Choose Quick Scan when the file is healthy and Advance Scan when it is damaged.
4. Accept the detected SQL Server version or select one yourself.
5. Click OK to open the file.
6. Click Close after the scan finishes.
7. Click Yes at the save prompt.
8. Save the scanned file in .str format and click OK.
9. Select an object in the tree to see its rows.

Export the Objects to the Destination

Eleven steps export the objects to the destination:

10. Click Export at the top.
11. Select the migration type SQL Server Database.
12. Type the Server Name with Authentication values for the destination.
13. Select the destination database.
14. Adjust Collation Settings if the application depends on them.
15. Tick the objects that you want in the new database.
16. Choose With only Schema for structure alone or With Schema & Data for rows too.
17. Click Save.
18. Close the Export Completed message by pressing OK.
19. Click Close and then Yes to save the report.
20. Select a Location and click OK.

Note: Online mode copies a live database without an MDF file. Steps for it sit in Transfer Data Between Two SQL Servers.

Which Checks Follow the Copy?

Check the state, the owner and the statistics of the new database. Three checks finish the copy:

1. Confirm that the database is online:

SELECT name, state_desc, compatibility_level, collation_name
FROM sys.databases
WHERE name = N'SalesDB';

2. Set the owner. Microsoft states that detach and attach sets the owner to the login that ran the wizard:

ALTER AUTHORIZATION ON DATABASE::SalesDB TO [CONTOSO\DbOwner];

3. Update the statistics as Microsoft recommends after a move:

USE SalesDB;
EXEC sp_updatestats;

Which Problems Stop the Copy of a SQL Database?

Five problems stop a copy:

Frequently Asked Questions

Author’s Verdict

Copy only backups protect the backup chain of the source and suit databases that remain online. Use them to copy a SQL database from one server to another without a break in the nightly backups. Detach and attach fits a quiet window when speed matters.

Copy Database Wizard adds logins but needs sysadmin rights on both servers. BACPAC files suit one portable package.

Check the owner and the statistics before users connect to the copy. Migration software handles MDF files when no source server exists.