How to Restore SQL Database with a Different Name?

  •   Written By
     
  • Published on
    November 24th, 2020
  • Updated on
    March 1, 2021
  • Read Time
    5 minutes

The common tasks that appear are to backup/restore the SQL database for further use. But sometimes users need to restore the full database in SQL Server with a different name. Well, during this blog, we are continuing to discuss a complete procedure to let users know how to restore SQL databases with a different name.

download

Use of Restore with Move option

Use of restore with move option allows to restore the full database but also classifies a new location for data files as .mdf and .ldf. If users attempt to restore SQL database from the backup file (.bak) then this is not mandatory. Besides if they are restoring the database with a separate file location then you may use this option. This option is useful to determine the name of the database file and the location of that file. Before applying this option, users are required to check the logical names for the files. Furthermore, check where the SQL Server can restore files if they do not use WITH MOVE option.

Manual Approaches to Restore SQL database with different name?

You can use different methods or approaches to restore SQL databases with different names.

  • Using Restore Transaction-SQL method
  • Restore an MSSQL Database in SQL Management Studio (SSMS)

Method 1: Using Restore Transaction-SQL Method

You can use the transaction-SQL method to restore SQL databases with different names.

 Follow the below-mentioned steps:

  • In the initial step, restore the backup file by giving a logical or physical name

RESTORE FILELISTONLY FROM DISK = ‘E:\Backup\PeterDatabase.bak’

  • Now, you can restore the entire database including MDF and LDF files.

RESTORE DATABASE PeterDatabase FROM DISK = ‘E:\Backup\PeterDatabase.bak’
WITH MOVE ‘PeterDatabase_Data’ TO ‘E:\SQLRestore\PeterDatabase_Data.mdf’
MOVE ‘PeterDatabase_Log’ TO ‘E:\SQLLog\PeterDatabase_Log.ldf’

  • At last, full restoration using the NORECOVERY option.

             RESTORE DATABASE PeterDatabase FROM DISK = ‘E:\Backup\PeterDatabase.bak’,
            WITH MOVE ‘PeterDatabase_Data’ to ‘E:\SQLDatabase\PeterDatabase_Data.mdf’,
            MOVE ‘PeterDatabase_Log’ TO ‘E:\SQLLog\PeterDatabase_log.ldf’,
            NORECOVERY

Finally, Run the Command.

RESTORE LOG PeterDatabase FROM DISK = ‘D:\PeterDatabase.trn’

Also Read: How To Fix SQL Database Error 824 – Causes & Solutions

(B). Restore an MSSQL Database in SQL Management Studio (SSMS)

This is another way to restore SQL databases with different names. Apply the approaches described below:

  • First, log on to SQL Server Management Studio.
  • Then, navigate to the database you need to restore.
navigate to database
  • Right-click Databases, and tap Restore Database. In the Source for restore section, choose From Device, and hit the browse button:
clickon browse button
  • Tap add in the Specify Backup window.  Browse to locate your recently restored flat files. Choose the Full backup file which should be the first backup file in the list:
choose backup of file
  • Hit OK; the Specify Backup window displays.
hit ok
  • Tap OK. Choose the database which you want to restore, select the backup sets to restore a section, choose the backup file you selected above in step 3:
backup sets to restore

7. In the left-hand pane, tap Options, and select the following:

  • In the Restore options section, choose to overwrite the existing database (WITH REPLACE), and remain the other options unselected.
  • In the Recovery state section, choose to Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

8. Click OK to execute the restore.

Using the following steps, you will be able to restore the SQL database with different names. Both the manual methods to restore the database to SQL Server are practical and helpful. You can either run the Transaction SQL command or SQL Server Management Studio to follow the interface system.

Besides this, the manual method is not error-free. You can face several issues like:

  1. It may show the invalid file format.
  2. The source path may be wrong.
  3. Both MDF and LDF file names may be incorrect.
  4. Insufficient memory space can make an error while restoring the SQL databases.

Additionally, inappropriate restoration can delete or damage the data. Now, you know that a wrong step can not only hold the restoration further and corrupt the database. To avoid such circumstances, use a professional approach.

Professional Approach

SysInfo SQL Database Recovery Tool is a complete Windows-based product that performs seamless recovery in no time and fix run-time error in SQL Server. It recovers important data from damaged MS SQL database files. The utility strongly restored SQL database objects without any annoyance. The SQL Database recovery tool can efficiently repair and recover corrupt MDF as well as NDF file extension without harming the primary data. Also, the interface of the software is so simple that even a beginner can use it without any technical expertise.

Key Attributes of SQL Database Recovery Software

  • The tool allows high accuracy recovery of file objects including all the elements and functions.
  • It is capable of performing MDF file recovery in Standard Mode and Advanced Mode.
  • The tools Support SQL Server ROW-compression and PAGE-compression.
  • The SQL Recovery tool is fully compatible with Windows 10, 8, 7, Vista, 2003, XP, and 2000.
download

Conclusion

Restore SQL database with a different name is a simple approach if you follow the crucial instructions. If you do the same all will look fine. Besides this, a single wrong step can corrupt the whole data. We suggest you a tool (SQL Database Recovery) to avoid such circumstances. It is capable of restoring the data even from the corrupted files.

Related Post