Backup SQL Server Database and Restore to Another Server | Guide


Table of Contents
  1. Case: Can I backup SQL Server database to another server?
  2. 4 methods to migrate SQL Server database to another server
    1. Method 1. Backup and restore SQL database from one server to another with SSMS GUI
    2. Method 2. Copy SQL database from one server to another server directly
    3. Method 3. backup and restore SQL database from one server to another using script
    4. Method 4. Centrally backup and restore SQL database between different machines
  3. Bottom line

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

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.

sql server

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.

backup task

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.

backup database

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.

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.

select backup file

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.

execute restore

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

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.

copy database 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.

destination server

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.

transfer method

4. Choose the databases you want to transfer. You can also choose to Move or Copy a database.

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

set up schedule

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

copying database

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'

sql restore full backup

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.

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

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


Is this information helpful?     

What can we do to improve this information? (Optional)
Refresh Please enter the verification code!