How to Restore SQL Database with Different Name: An Ultimate Guide

Daniel Jones | February 28th, 2019 | SQL |

“Last two week, I have a complete backup of Database1. The backup is finished weekly in the task scheduler and I receive a .bak file. Now I want to restore SQL database with different name (Database2). Can anyone suggest me a trustworthy solution for the same? Any assistance would help clarify.”

Nowadays, the most common task for DBA is to restore a database for testing and development purpose. But sometimes users want to restore full database in SQL Server with different name. Well, in this blog, we are going to discuss a complete procedure to let users understand how to restore SQL database with different name.

Use of RESTORE..WITH MOVE Option

Moreover, the RESTORE … WITH MOVE option enables to restore full database, but also identify a new location for data files such as .mdf and .ldf. If users try to restore an existing database from the database backup then this is not necessary, but if they are restoring the database with different file location then you may use this option. This option allows to determine the name of database files and what is the location for created files. Before using this option, users need to check the logical names for the files and where the SQL Server can restore files if they do not use WITH MOVE option.

If the another database already exists with same file name when users try to restore and the database is online the restore failed. However, if the database is offline due to some reason and files cannot be opened, restore will overwrite the data if users do not use WITH MOVE option, so be sure to not accidently overwrite great database files. Further, when using WITH MOVE option make sure that the account used for SQL Server engine grant permissions to create files in the specified folder.

Restore SQL Database with Different Name – Top 2 Approaches

Go through the following methods by which users can easily restore SQL database with different name. Please have a look:

Approach 1: Using T-SQL (Transact-SQL)

First of all, you have to determine the logical name and physical location for each file. This can be done by using RESTORE FILELISTONLY command. It will provide you both logical as well as physical names.

Here is the example:

 

  • Restore complete backup WITH MOVE

Now, you need to restore the database, but you have to put database files in “G:\SQLData” folder and transaction log files in “H:\SQLLog” folder. The command will be shown like this:

  • Restore full & transaction log backup WITH MOVE

This WITH MOVE option only be specified for first restore, because after this the database is found in “restoring” state. And the second restore can write the content to a new location which is being used.

Approach 2: Use SQL Server Management Studio

Follow the below steps to restore SQL database using SSMS:

  • On the options page for restore, you need to change “Restore As:” value for each file as given below. This below restores to the root folder, but you can change it by selecting G:\SQLData\ and H:\SQLLog\ as required.

Wrapping Up

Sometimes users have deleted some records in that case, they need to create another database with different name. So, in this blog, we have discussed a step-by-step procedure to restore SQL database with different name. Make sure you follow all the above steps very carefully.