By Alison / Last Updated June 20, 2022

Case: Can I Restore SQL Backup to A New Database with Different Name?

 

I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler and I get a .bak file. Now I want to fiddle with some data so I need to restore it to a different database - Database2. Is there any other way of restoring it to Database2, or at least, how do I browse through the data of that .bak file?

- stackoverflow.com

In some cases, we may want to restore SQL backup to new database with a different name to distinguish it from the source database. Sometimes we may even want to transfer the database to another instance or server for data migration.

So how do we restore backup to new database in SQL Server? In this article, I summed 3 methods to help you accomplish this. If you find them complicated, you can also choose professional software.

Solutions to Restore SQL Backup to New Database

The following 3 methods are applicable to different scenarios. If you want to restore SQL backup to new database with different name, please choose Method 1 or 2. If you want to migrate database from one instance/server to another, all the methods apply, but Method 3 is a more direct approach.

Tip: Before you begin, please make sure no one else is using, or you may get your SQL database stuck in restoring.

Method 1: Use SSMS GUI to Restore SQL Backup to New Database

1. Launch Microsoft SQL Server Management Studio (SSMS), and connect to your target instance. Now please don’t create a new empty database to receive the data from the backup, you just need to right-click Databases and choose Restore Database.

Restore Database

2. In the prompt window General page Source section, select Device > … >Add, to specify the location and select the backup file you want to restore. The name will be filled automatically in the blank of Database. Then click OK.

Select Backup File

3.  When you restore SQL backup to the same server, the operations next will be different from restoring to another server.

Restore to the Same Server with Different Name

In Destination > Database, write a new name which is different from the former one.

Click OK, and it will restore the backup to a new database.

Restore Database Newname

Restore to Another Instance/Server

You can also restore SQL Server database backup from bak file to another server, but in this case you don't have to change the name. *If you really want to change it, you can rename the database in the blank of Database, Destination section.

Select Files in the right menu, and Relocate all files to folder in Restore database files as section. Then Click OK. When it’s done, Refresh the database list and you can see the database is here.

Files Relocate

Method 2: Use T-SQL Commands to Restore SQL Backup to New Database

1. Launch SSMS and connect to your server, right-click the target instance name and select New Query.

New Query

2. Input following statements to find the logical filename of your backup.

RESTORE FILELISTONLY FROM DISK='filepath\backup.bak'

Note: In ‘filepath\backup.bak’ you need to write where your backup is putted in and the file name of your backup. For example: C:\mydb\backup.bak

3. Click Execute and you will see the logical filename in Result section.

Logical Name

4. Input following statements to restore this backup to new database(If you are restoring the backup to another server, you don't need to rename the database). You don’t have to create a new database before either.

RESTORE DATABASE newname FROM DISK='filepath\backup.bak'
WITH
   MOVE 'logical_filename' TO 'filepath\newname.mdf',
   MOVE 'logical_filename_log' TO 'filepath\newname_log.ldf'

Restore to the Same Server with Different Name

As for the 'filepath\newname.mdf' and 'filepath\newname_log.ldf' part you need to write the path where the new database should be put in. 

Rrestore with move

If you don't know where the folder is, you can right-click your target instance and select Properties > Database Settings > Data to find out.

Database Properties Data

Restore to Another Instance/Server

Before you start, make sure your target instance has the permissions of access to the backup file.

In 'filepath\newname.mdf' and 'filepath\newname_log.ldf' you need to write the path of the target instance, instead of the original instance.

Click Execute. When you see the result of success, click Refresh, and the database is in list.

Restore to Another Server

Note: If you received the error report: Cannot open backup device. Operating system error 5 (Access is denied), the reason could be that the account you use to log in to SQL server does not have permission to read or write to the target share. You can view this article to learn solutions: SQL Server Backup to Network Share Access Denied

Method 3: Use Detach/Attach to Move SQL Database to Another Instance/Server

1. Right-click the database and select Tasks > DetachIf you haven’t backup the database yet, Detach/Attach is a convenient way for you to directly move database from one instance/server to another.

Database Detach

2. In the prompt window, select Drop Connections and Update Statistics as need. Click OK to detach the database.

Detach General

The database will be detached then, but the files will remain in the same folder.

3. Right-click Database in another instance, select Attach.

Database Attach

4. Click Add… in the prompt window, find the database files in their correspondent folder, normally they are still in DATA, end with .mdf and .ldf.

If you can’t find them, you can just select the correspondent folder and input filenames in search box, then click OK directly, and they will be shown in database details section.

Click OK to attach the database to this instance.

Attach Databases

And if you just want to copy the database to another instance/server, and you are not using the Express version, Copy Database Wizard is a simpler tool for you. To learn how you can view this article Backup and Restore SQL Database from One Server to Another

One Centralized Way to Backup and Restore SQL Backup to Another Instance/Server

To make the restoration to another server easier, you can choose AOMEI Centralized Backupper Database, to use simple clicks to backup and restore SQL database to another instance, even another computer within LAN. And it is compatible with all windows PCs and Servers.

Compared to traditional ways, this software is easier, and it doesn’t require operations on each server seperately, you can centrally backup and restore on the computer which installed this software.

Here’s a 30-day Free Trial for you:

Download Free TrialWin PCs & Servers
Secure Download

To properly install Centralized Backupper Database, you can follow these steps:

Launch Centralized Backupper Database, and install Agent remotely or manually by clicking Computers > Install Client Program.

Install Client Program

For the computers with client programs installed manually, you still need to Request Control over them for the further operation.

Click Computers > Uncontrolled Computers to find the correct IP, then click Request Control on the upper bar. A prompt window will show on their computers, once all permission received you can start whenever you like.

Request Control

Backup and Restore SQL Database to Other Instances/Servers

Select Tasks > New Task > SQL Server Backup to create a new backup task. Follow the 3 steps in the prompt window.

In Step 1, click Add Computers to detect all the controlled computers with SQL instances, select the correct IP and click OK.

Add Computers

In Step 2, click Add and you can select multiple databases you want to backup.

Add All Instances

In Step 3, click Add Storage to add a Share or NAS Device as a storage end, select a path. Then click Start Backup to start.

Share Nas Storage

How to Restore SQL Backup to Another Instance or Computer

1. The created backup task will show in Tasks tab, you can manage or restore it here instead of locating a specific bak file on the disk. Just click Tasks, and click Advanced on its upper left, select Restore to continue.

Restore Database

2. In this step you can select the computer and specific backup (full/differential) to be restored from, and you can select databases in it as you like.

Select Specific Backup

3. Choose to restore the database to the original location or a new location. By selecting the second option, you can specify another instance on the same or another SQL Server in the LAN. Then click Start Restore to accomplish this task.

Restore to A New Location

Summary

In this article I introduced 3 ways to restore SQL backup to new database, or to another server. Hope this could help you.

If you think these ways are not intuitive enough, you can also choose a simpler tool: AOMEI Centralized Backupper Database Edition. In addition to SQL database, it is also a centralized backup software for computer files, partitions, disk or OS. You can even sync folders from multiple computers to a central network location.