Transfer SQL Server Database From One Computer to Another – 3 Ways
In this blog, learn how to transfer SQL Server database from one computer to another using these 3 methods:
- SysTools SQL Server Migration Assistance
- Microsoft SQL Server Management Studio
- SQL Commands for Backup & Restore
SQL Server Database Migration Software
This is an automated tool developed by SysTools. 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
Freely Export 25 Items
So according to your needs you can choose options. Here I will tell you basic steps you have to go through.
Learn How to Copy SQL Server Database From One Computer to Another Using SysTools:
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. 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’.
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”.
Using Microsoft SQL Server Management Studio (SSMS)
Follow these steps:
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
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.
Using SQL Commands for Backup & Restore
Following are the SQL commands that you can use to backup and then restore the database on another computer.
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
