Backup SQL Server Database and Restore to Another Server | Guide

---

Case: Can I backup SQL Server database to another server?

You may need to copy a SQL database from one server to another, or computer to computer, for various reasons such as testing, checking consistency, restoring a database from a crashed machine, or working on the same project on a different machine.

You can back up and restore a SQL Server database using several methods, including using the SQL Server Management Studio (SSMS) backup and restore feature. This allows you to create a copy of your database and restore it if needed, providing a safeguard against data loss.

4 methods to migrate SQL Server database to another server

There are 4 commonly used methods to backup SQL databases from one server to another, which include [insert methods here].

Method 1, which involves using SQL Server Management Studio (SSMS), is a less error-prone approach to implementing the task, but it does require downtime as it involves backing up and restoring the database.

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.

For most people, a professional centralized backup solution is introduced, which provides a user-friendly interface to conveniently and centrally backup databases from server to server.

It's not allowed to restore a SQL Server database backup to a lower version of SQL Server. For example, you can't restore a backup from SQL Server 2016 to 2012, but you can restore one from 2012 to 2016.

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 file and restore to any other compatible SQL server, regardless of the source and destination platforms. The process involves creating a backup of the database in the source server using the backup wizard in SSMS, then copying the .bak file to the destination server. Once the file is copied, you can restore the database from the .

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. To create a full backup of your SQL Server database, ensure the backup type is set to "full backup". Then, select a destination for the backup file. By default, it will be saved in the C:\Program Files\Microsoft SQL Server\ (Instance name) \MSSQL\Backup directory. You can remove the default path and add a more accessible one. Click "OK" to initiate the backup process.

3. Copy the .bak file to the target machine, either by copying it directly or by copying it to a network share and then copying it to the target machine's local folder.

If you want to directly backup a SQL database to a network share, you can follow the steps outlined in the article linked above. This process allows you to automatically save your database to a network location, providing an easy and efficient way to maintain a backup of your SQL database.

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 choose the backup .bak file from your local drive, or manually enter its file name and click OK if you can't find it directly.

If the database you're trying to restore from a .bak file doesn't already exist in the target instance, don't create an empty database to restore into, as this will trigger error 3154, stating the backup set is for a database other than the existing "model" database. Instead, let the database name in the backup file automatically become the new database name, or manually modify it in the destination database textbox.

6. Confirm the restore settings and click OK to execute it.

If the restore of database 'example' failed due to "The operating system returned the error '5(Access is denied)'…", switch to the Files tab, check Relocate all files to folder, and click OK to try again.

Note: SQL Server 2016 uses a different default path than earlier versions, requiring database files from earlier versions to be restored to a new location if you want to restore them to SQL Server 2016.

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. This tool can move or copy databases and certain server objects between different instances of SQL server. However, before using it, there are some limitations you should be aware of, such as those listed on a webpage that can be accessed by clicking here.

• Copy Database Wizard is not available in the Express edition.
• The SQL Server Migration Assistant (SSMA) for databases cannot move or copy databases to an earlier version of SQL Server.
• It cannot be used to move or copy system databases.
• This rule does not apply to databases that are designated for replication or have been marked as inaccessible, loading, offline, or recovering, among other statuses.

To transfer databases, follow this guide if you're okay with it. The steps include.

1. To copy a database in Microsoft SQL Server Management Studio, right-click the desired database, select Tasks, and then choose Copy Database to initiate the wizard.

2. To begin, click "Next" on the welcome screen. Then, choose a source server that requires authentication and click "Next" again. Finally, select a destination server in the same manner.

3. The detach and attach method is safer but requires the source database to go offline, making it suitable for upgrading databases or moving large ones. In contrast, the SQL Management Object method is slower but allows the database to remain online.

4. Select the databases you want to transfer, and also choose whether to move or copy them.

5. Configure the destination for each database you want to transfer, then you can configure the Integration Service package created by this wizard, choosing 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.

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

You can backup and restore a SQL database using a T-SQL script if you're familiar with scripting.

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 using methods such as FTP, SCP, or shared network drives.

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

SQL Server Management Studio (SSMS) can only backup one database at a time, making it laborious for multiple databases or instance migrations. The Copy Database Wizard can move or copy multiple databases, but it has limitations, such as not working in Express editions.

To backup and restore SQL Server databases flexibly, you can use Qiling Backup, an alternative to the built-in SQL Server Backup feature. With Qiling Backup, you can select multiple databases from multiple instances to backup, and then restore them to their original location or another controlled machine, all through an intuitive graphical user interface.

Besides, Qiling Backup also comes with various features that make it the best choice for SQL backup.

This tool is compatible with various operating systems and SQL Server versions, allowing for centralized management of backup tasks for PCs, servers, and virtual machines within a local area network.

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 The script helps you backup your database automatically, with a schedule setting enabled by default. This allows you to edit or disable the schedule as needed. Additionally, you can choose the backup method, such as Full, Incremental, or Differential, based on your preferences.
✍Backup Cleanup This feature automatically removes history backup versions based on a set rule to save storage space.
✍Email Notification It allows you to receive email notifications when the task is abnormal or successful.

Completed tasks will be listed in the Backup Task tab, allowing for editing or restoring from the SQL database as needed.

Bottom line

Here are the 4 methods to backup SQL server database to another server: SQL Server Management Studio offers options to restore SQL Server database from bak file to another server or directly move/copy databases to another server, or use SQL Server Backup feature of Qiling Backup to manage multiple databases at once.

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.