How to Restore SQL Database with Different Name Easily
Table of Content
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.
Before we move ahead, users need to understand that it is not an easy method of SQL server restore database from backup with different name if they are unaware of modern tactics. Other than that, with the right tool, this task can be quite easy.
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 identifies 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 a different file location then you may use this option.
This option allows determining 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 restore database with move option.
If 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 accidentally overwrite great database files.
Further, when using this option, make sure that the account used for SQL Server engine grants permission 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 solve the issues. 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. This automated approach can even fix Microsoft SQL Server login failed error 18456 & several other errors as well.
Step-1. Download the tool on your system to restore database with move option.
Step-2. After this, run it and add the MDF file.
Step-3. Choose a scan mode and MDF file version.
Step-4. Preview complete database and hit Export button to continue restore SQL database with different name.
Step-5. Choose SQL Server Database as export option.
Step-6. Now, choose Create New Database option and provide a different name for the database as per your choice. Now finally , move further to restore database with move or just like how you wanted.
Step-7. Choose the database object you want to restore and click Export button.
Also Read: Difference Between Nolock and With Nolock In SQL Server Database Explained
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.
Finally, we can say that SQL server restore database with different name task is easy for users using the automated solution. Otherwise, the manual solution is full of risk for the SQL server restore database to different name operation.