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.

4 methods to migrate SQL Server database to another server

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.

Method 1. Backup and restore SQL database from one server to another with SSMS GUI

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.

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

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):

• 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. 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.

Method 3. backup and restore SQL database from one server to another using script

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'

Method 4. Centrally backup and restore SQL database between different machines

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 - Qiling 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, Qiling Backup also comes with various features making it the best choice for SQL backup.

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:

• Free Download
• Windows 11/10/8.1/8/7 & Servers, 100% Secure

Next we will give you the detailed guide on how to backup SQL server database to another server with this tool.

✍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.

Bottom line

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 Qiling Backup would be a simpler choice.

Related Articles

• Schedule Database Backup in SQL Server 2012/2014/2016/2019
  You will learn how to schedule database backup in SQL server 2012/2014/2016 using SQL Server Agent jobs and SQL Serve Maintenance Plans. Also, an easier way is included.
• Best Server Centralized Backup Strategy for Multiple Windows Servers
  Do you need a centralized server backup strategy to protect multiple Windows servers or PCs on network? Here's a simple solution for home or business use.
• Windows Server Backup Bare Metal Recovery to Different Hardware
  It's easy to create a Windows Server Backup for Bare Metal Recovery, but can you restore it to another machine with different hardware? How to do it without boot errors?
• How to Restore SQL Database from Bak File? (3 Methods)
  After completing the backup successfully, you can restore SQL database from bak file any time you need. In this post, you will find 3 methods to do this, including SSMS, T-SQL query and command line.