Backup SQL Server Database and Restore to Another Server | Guide

Case: Can I backup SQL Server database to another server

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.

3 methods to migrate SQL Server database to another server

For large volumes of data, the most secure way is to backup SQL database and restore to another server. It is less error prone than other methods, but also requires a downtime when migrating database. So if you cannot afford any downtime, please choose Method 2 and copy database with the “SQL Management Object” mode.

If you want to backup and restore multiple SQL databases of different instances between machines conveniently, you could also try Method 3 for centralized management.

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.

• Method 1. Backup and restore SQL database from one server to another
• Method 2. Copy SQL database from one server to another server directly
• Method 3. Centrally backup and restore SQL database between different machines

1. Backup and restore SQL database from one server to another

To backup and restore a database to another instance of 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 remote location such as 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.

2. Copy SQL database from one server to another server directly

There’s a more direct way to transfer SQL databases - Copy Database Wizard. With it, you don't need to backup SQL Server database to another server, but move or copy databases and certain server objects between different instances of SQL server directly.

But before using it, there are some limitations of this tool you should know (click here for more details):

• Copy Database Wizard is not available in the Express edition.
• It cannot move or copy databases to an earlier version of SQL Server.
• It cannot be used to move or copy system databases.
• It does not apply to databases that are marked for replication, or marked Inaccessible, Loading, Offline, Recovering, etc...

If you are ok with them, then follow the guide to transfer databases:

1. 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 it immediately, or set up a schedule to run it regularly.

6. When the wizard is complete, click Finish, and the copy process will begin.

3. Centrally backup and restore SQL database between different machines

In SSMS, SQL backup 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, such as not being able to work in Express editions.

Therefore, I'd like to introduce an alternative, AOMEI Cyber Backup, to help you backup & restore flexibly. 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 intuitive GUI.

This tool is compatible with machines running Windows 11, 10, 8.1/8, 7, Windows Server 2022, 2019, 2016, 2012, 2008 (R2), and supports backing up SQL Server 2005 to 2022.

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.

You can download the 30-day free trial and get a time-limited discount:

🔹How to backup SQL Server databases:

1. Install the the downloaded .exe file and launch the main interface of AOMEI Cyber Backup, click Source Device -> Microsoft SQL -> Add Microsoft SQL.

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  -> Authentication to validate the database instance. 

3. Click Backup Task on the left menu bar, select Backup Task -> Create New Task, and choose the backup type as Microsoft SQL Backup.

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.

5. Select Target to store the backup. You can specify a local or network path, the added path will be saved in Added storage.

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.

🔹How to restore SQL database from backup:

1. Click Backup Task on the left menu bar, locate the task you want to restore, and click  -> Restore.

2. Select a backup version that you want to restore database from.

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.

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.

Bottom line

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.