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

As you know, 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 on its use, such as not being able to work in Express editions.

Therefore, I'd like to introduce an alternative, AOMEI Centralized Backupper Database, 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 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. 

Besides SQL databases, it supports Windows files, partitions, disks and OS as well. You can easily manage backup tasks for all supported PCs, servers and virtual machines within LAN from a central machine.

You can download the 30-day free trial and get a up-to 60% off discount:

How to install and setup the software correctly:

Install AOMEI Centralized Backupper Database Edition on the central machine, and the Agent packages on client computers.

Launch the software and go to Computers tab, select the computers you want to manage from the list, and click Request Control to gain control of these client computers. Of course, you can control your own computer as well.

How to backup SQL Server database with Centralized Backupper Database:

1. Choose SQL Server Backup for Home tab, or from the drop-down menu of New Task in Tasks tab.

2. In Step 1, click on + Add Computers to detect all the controlled computers. The client computers with SQL Server database will show in the pop-up window. Choose the one you want to backup and click OK.

3. Now let’s move to Step 2. Select the client computer on the left, and Click + Add to detect all the SQL Server instances on the selected client computers. Then you can select different databases on different instances as you like, then hit OK.

You will be required to enter the credential information to connect the selected instance. Windows authentication and SQL Server authentication are optional.

4. Select a destination path in Step 3. Click Add Storage to add a share or NAS device as the storage end. Then you can choose a specific path to store this backup. Click OK to confirm the setting, and then Start Backup.

Before starting, you can also click Settings to enable email notification, or click Schedule to set up auto backup Once/Daily/Weekly/Monthly. Full backup and differential backup are all supported.

How to restore SQL Server database backup to another server:

1. Find the created SQL Server database backup task under Tasks tab. Click Advanced at the top-right corner of it and choose Restore.

2. Select the computer and the specific databases to be restored, then click Next.

3. To restore database to another server, please choose Restore to a new location. Then you can select another controlled machine as the target location, and click Start Restore to exit it.

Overwrite the existing database (s): 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.

For questions and problems about using this software, you could refer to this AOMEI Centralized Backupper Database FAQs.

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 Centralized Backupper Database Edition would be a simpler choice.

Not just backup SQL Server database to another server, the software is also an all-around data protection and disaster recovery solution for your Windows PCs and Servers. With the centralized management and complete backup & sync features, you can use it to build your small business backup strategy easily and cost-efficiently.