How to backup SQL Server databases to another server? This article introduces the detailed steps to migrate databases between different machines. 4 methods are included.
How to use SQL Backup to restore to a different server?
Hi, I am using SQL Backup to backup all my db servers. My question is about two servers - both servers have a db with the same name. I need to backup the database from server 1 and restore it over the top of the database on server 2. Can I do this with SQL backup?
Sometimes you may want to copy SQL database from one server to another, or computer to computer. The possible scenarios include testing, checking consistency, restoring database from a crashed machine, working on the same project on a different machine, etc.
And yes, you can do this through several methods - including but not limited to SSMS backup and restore.
There are 4 commonly used methods to backup SQL database from one Server to another listed in this part.
Method 1 guides you to implement the task with SSMS. It is less error-prone but requires downtime when backing and restoring.
If you can't afford any downtime, you can go to Method 2 to copy database with "SQL Management Object" mode.
Method 3. is for users who familiar with T-SQL script.
Method 4. Introduce a professional centralized backup solution for most people. It uses a user-friendly interface to conveniently and centrally backup database from server to server.
Note: Whichever method you choose, it’s not allowed to restore SQL Server database backup to a lower version of SQL Server. For instance, you could restore a backup of SQL Server 2012 to 2016, but not the other way around.
To backup and restore a database to another instance of SQL Server, you can use SQL Server Management Studio to backup to a bak. files and restore to any other compatible SQL server. The source and destination computers can be any platform on which SQL Server runs. Here are the steps:
1. Launch SQL Server Management Studio and connect to the instance you want to backup. Then right-click the specific database, choose Tasks > Back Up.
2. Make sure the backup type is full backup, and then select a destination. By default, the backup file will be saved in C:\Program Files\Microsoft SQL Server\ (Instance name) \MSSQL\Backup. You can also Remove it and Add another path that is easier to find. Click OK to execute backup.
3. Copy the .bak file to the target machine. If it’s network connected with the source machine, you can also copy the backup to a network share, and then launch the target machine to copy it to local folder.
If you want to directly backup SQL database to network share, please click on the anchor text for more details.
4. Launch SSMS on the target server, connect to the instance you want to restore to. Then right-click Databases and choose Restore Database.
5. In the popping out window, select Device and click on the omitted symbol to select the backup .bak file from your local drive. If you cannot find it directly, please locate to its storage path, and manually type its file name, then click OK.
If the database you want to restore doesn’t exist in the target instance, please don’t create an empty database to restore the .bak file, or you will receive error 3154 saying the backup set holds a backup of a database other than the existing “model” database. The database name in the backup file will be auto used as the new database name. You could also modify it in the textbox of the destination database.
6. Confirm the restore settings and click OK to execute it.
If the restore of database 'example' failed because “The operating system returned the error '5(Access is denied)'…”, please switch to Files tab, check Relocate all files to folder and click OK to try it again.
Note: SQL Server 2016 uses a different default path than earlier versions. If you want to restore databases from an earlier version to SQL Server 2016, it’s necessary to restore files to a new location.
There’s a more direct way to transfer SQL databases - Copy Database Wizard. It could move or copy databases and certain server objects between different instances of SQL server. But before using it, there are some limitations of this tool you should know (click here for more details):
If you are ok with them, then follow the guide to transfer databases:
1. On the Microsoft SQL Server Management Studio, right-click any database you want to copy, choose Tasks > Copy Database to enter the wizard.
2. Click Next on the welcome screen, select a source server with authentication and hit Next. Then select a destination server in the same way.
3. Select a transfer method. The detach and attach mode is safer but requires the source database to go offline. It’s best for upgrading databases or moving very large databases. The SQL Management Object method is slower but the database can remain online.
4. Choose the databases you want to transfer. You can also choose to Move or Copy a database.
5. Configure the destination for each database you want to transfer, then you can configure the Integration Service package created by this wizard, choose to run immediately or set up a schedule to run it regularly.
6. When the wizard is complete, click Finish, and the copy process will begin.
If you are familiar with script, you can backup your SQL database and restore to another server with T-SQL script.
Step 1. Connect to your distance, click New Query and enter the appropriate backup commands, then hit Execute to implement it.
Step 2. Use the command to create a backup on the source server:
BACKUP DATABASE DatabaseName TO DISK ='FilePath\FileName.bak'
Step 3. Once the backup is created, transfer the backup file (YourDatabaseName.bak) to the destination server. You can use methods like FTP, SCP, or shared network drives for this transfer.
Step 4. Connect to the distance you want to restore database, and use the command below to restore:
RESTORE DATABASE DatabaseName FROM DISK = 'FilePath\FileName.bak'
As you know, SQL backup with SSMS can only back up one database at a time. If you have multiple databases or even instances to migrate, the task will become laborious. And although Copy Database Wizard could move or copy multiple databases, there are many restrictions on its use, such as not being able to work in Express editions.
Therefore, I'll introduce an alternative to help you backup & restore flexibly - AOMEI Cyber Backup. With the SQL Server Backup feature, you could select multiple databases of multiple instances to backup, and restore them to original location or another controlled machine with an intuitive GUI.
Besides, AOMEI Cyber Backup also comes with various features making it the best choice for SQL backup.
Central control: With a user-friendly console, a backup administrator can easily backup and restore multiple SQL databases.
Automatic backup: Create a backup schedule, this backup solution will automatically run your backup task daily, weekly, monthly, and weekly.
Smart and timely notification: AOMEI Cyber Backup provides a timely backup report mechanism, enabling you to stay informed about the backup status.
Complete enterprise backup solution: Besides SQL databases, it supports virtual machine backup as well. You can easily manage backup tasks for all supported VMware ESXi and Hyper-V VMs from a central machine.
Also, this tool is compatible with machines running Windows 10/ 8.1/8/7/Vista/XP, Windows Server/2003/2008 (R2)/2012 (R2)/2016, Windows SBS 2003/2008/2011, Windows Home Server 2011 (32/64-bit), and SQL Server 2015 to 2019. You can easily manage backup tasks for all supported PCs, servers and virtual machines within LAN from a central machine.
Here's a 30-day free trial for you to try:
Step 1. Install the the downloaded .exe file and launch the main interface of AOMEI Cyber Backup, click Source Device -> Microsoft SQL -> Add Microsoft SQL.
Step 2. Download proxy program and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device with SQL installed. Next, click
Step 3. Click Backup Task on the left menu bar, select Backup Task -> Create New Task, and choose the backup type as Microsoft SQL Backup.
Step 4. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one or multiple database flexibly.
Step 5. Select Target to store the backup. You can specify a local or network path, the added path will be saved in Added storage.
Step 6. Click Start Backup to create and execute the task. In daily use, you can also use some useful features to optimize the backup:
✍Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs. Also choose the backup method (Full, Incremental or Differential) as you like. ✍Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space. ✍Email Notification enables you to receive email notifications when the task is abnormal or successful.
A completed task will be listed in the Backup Task tab. Then you can edit or restore from SQL database from it when you need.
Step 1. Click Backup Task on the left menu bar, locate the task you want to restore, and click
Step 2. Select a backup version that you want to restore database from.
Step 3. Select the target location that you want to restore to. The default option is Restore to original location, you can choose Restore to new location instead, and then select the target, specify the name of the new database, etc. Click Start Restore to confirm and execute the operation.
Overwrite the database with the same name: If you tick this option, the backed up databases will overwrite the target databases of the same names. If you leave it unchecked, the target databases with same names will be omitted during restoring.
Step 4. After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the button to see restore details or cancel it.
Here we list 4 methods of how to backup SQL server database to another server. It is important to choose a migration method that matches your unique requirements and current environment. You can choose SQL Server Management Studio offers you options to restore SQL Server database from bak file to another server, or directly move/copy databases to another server. But if you want to manage multiple databases (including system databases) at once, the SQL Server Backup feature of AOMEI Cyber Backup would be a simpler choice.