Sql server restore database from backup with different name

Daniel Jones | Modified: 2022-01-17T19:39:01+05:30|SQL | 4 Minutes Reading

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.

USER QUERY

“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.”

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

What to do if manual methods fail?

If the above-mentioned manual methods are not capable to restore SQL database with different name. Due to corrupt or damaged database, then try its alternative solution. For a quick and perfect results make use of SQL Repair tool. It provides numerous advanced features to its users for a hassle-free recovery process. Moreover, the tool completes the process in a few simple steps in the following way as described below-

1. 1. Download the tool on your system.

Purchase Now
100% Secure Free Download
100% Secure

1. After this, run it and add the MDF file.
   
2. Choose a scan mode and MDF file version.
   
3. Preview complete database and hit Export button.
   
4. Choose SQL Server Database as export option.
   
5.  Now, choose Create New Database option and provide a different name for the database as per your choice.
   
6. Choose the database object you want to restore and click Export button.
   

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 step-by-step procedure to restore SQL database with different name. Make sure you follow all the above steps very carefully.

How do I restore a SQL database with a different name?

How do I restore a specific database from a specific backup file with specific date and time?

How do you backup and restore SQL database from one server to another?

How do I restore a SQL backup from a diff?