Transfer SQL Server Database to Another Computer – 3 Ways

Summary: In this blog, we’re going to learn how to transfer SQL server database to another computer using the top 3 methods. We are pretty sure that users can easily get the perfect solution after reading this entire guide. However, they must be attentive throughout the blog to migrate SQL database from one server to another without any errors.

1. SQL Server Migration Tool
2. Microsoft SQL Server Management Studio
3. SQL Commands for Backup & Restore

Using Microsoft SQL Server Management Studio (SSMS)

Follow these steps to how to transfer a database from one computer to another using SSMS:

Step-1. Right-click the on database that you want to transfer and select Tasks >> Back up.

Step-2. The Backup type must be Full.

Step-3. Click Add… button and designate the location and also the backup name.

Step-4. Copy the backup file that has been created to another computer for learning how to send SQL database file to another computer safely.

Step-5. On another computer, In SSMS, right-click the SQL Server instance and select Restore Database.

Step-6. Select Device radio button and click the ellipsis button […] to navigate to the copied backup file.

Also Read: How to Import Table Data to Another Database in SQL Server

SQL Server Database Migration Software

This is an automated tool to copy SQL Server database to another computer. Evidently, this software can help you to copy and transfer SQL data in 4 ways:

• Copy live SQL Server Database to Another Live SQL Server
• Convert Live SQL Database Server Data to SQL Script
• Export Offline SQL file to live SQL Server
• Convert Offline SQL file to SQL Script

So according to your needs you can choose options. Here I will tell you basic steps you have to go through.

Also Read: Copy Table from One Database to Another Database

Learn How to Copy SQL Server Database From One Computer to Another Using the Software:

Step-1. Download the software on your system and click on ‘Open’ to load SQL files.

Step-2. Here you have to choose between Online/ Offline process. Choose ‘Online’, if you are copying SQL databases from one live server to another or to SQL Scripts. Choose ‘Offline’, if you are transferring your offline SQL/ MDF file to a live server or SQL script.

Also, whatever you choose according to your source and destination location, the software will ask you to provide some details regarding it. Click on OK after that.

Step-3. The tool will start scanning the database and preview the results. Then, click on ‘Export’ in learning how to send SQL database file to another computer task.

Step-4. Here you have to choose the destination types where you want to export to/as “SQL Server Database” or “SQL Server Compatible SQL Scripts”.

Step-5. Then, you can specify whether you want to export data and Schema or Only Schema and choose the object type as well. Again click on “Export” to migrate SQL database from one server to another.

Note: This tool can also migrate SQL server script to live database as per users’ requirements.

Also Read: Best Way to Migrate SQL Server 2016 to 2019 Verison

Using SQL Commands for Backup & Restore

Following are the SQL commands that you can use to back up and then restore the database on another computer. This is compulsory to transfer SQL server database to another computer manually. SQL server migration best practices do not recommend following this manual method at all.

To Back Up a Whole Database Use This:

BACKUP DATABASE { database_name | @database_name_var }

  TO <backup_device> [ ,…n ]

  [ <MIRROR TO clause> ] [ next-mirror-to ]

  [ WITH { DIFFERENTIAL

           | <general_WITH_options> [ ,…n ] } ]

[;]

For More Info on Backup: http://msdn.microsoft.com/en-us/library/ms186865.aspx

To Restore the Entire Database Backup Use This:

RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,…n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,…n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <FILESTREAM_WITH_option>
| , <service_broker_WITH options>
| , \<point_in_time_WITH_options-RESTORE_DATABASE>
} [ ,…n ]
]
[;]

For More Info on Restore: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Apart from these, there are no commands left for users to copy SQL Server database to another computer manually. Although we do not recommend this solution to users with core technical knowledge may use them in case of emergency. Otherwise, the advanced tool is way better than this.

Conclusion

Finally, we can say that now users can easily transfer SQL server database to another computer with ease. Selecting the right tool plays a significant role here for users to get the desired results. With the automated tool, users don’t need to be technically wise. The automated solution is quite advanced & allows users to execute the task without having the core knowledge of technicalities.