Restore SQL Database With a Different Name

Aftab Alam Aftab Alam Updated On - 25 Oct 2019

When you want to restore a SQL database from a backup file, then you should know that to restore an encrypted database, you must have a certificate or asymmetric key which is necessary to encrypt the database. If you do not have that key or certification, then you cannot perform the restore procedure. You must keep the key or certificate safe as long as you want to save the backup file and later restore it.

The benefit of restoring the MDF files from a BAK file is that even if you restore an older version of the database to SQL Server 2017, then it will upgrade to 2017 automatically. It saves you from installing another version of SQL server, and it also does not affect the database compatibility level.

Restore a backup with a new database name where the original database exists

In the following example, we will restore a backup of HR and save it as a new database HRTest. The initial database of HR is also present at the SQL Server.

  1. Connect to your current SQL server using SQL Server Management Studio.
  2. Under Object Explorer, go to Databases. Right-click and select the option Restore Database.
  3. Select the Device option and click the Browse button.
  4. Click the Add button to add the backup file (BAK) from its location.
  5. Click OK after selecting the BAK file.
  6. Click the OK button.
  7. The restore wizard adds the BAK file as the source. Now go to the destination tab and change the name of the database to HRTest. It will be the name of the new database where the data will reside after restoration
  8. Go to Options and check the option ‘Take tail-log backup before restore.’ Click OK.
  9. A successful message will show you that Database ‘HRTest’ restored successfully.
  10. This way you can restore a database to the SQL server with a different name even if the current database is present there.

    You can also use a Transaction-SQL method to restore the backup file into a database with a different name.

    RESTORE DATABASE HRTest FROM DISK=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Test.bak’
    WITH
    MOVE ‘HR’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HRTest.mdf’,
    MOVE ‘HR_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HRTest_log.ldf’

    The first command restores the backup file of Test to the database HRTest. HRTest is the new database.

    The second command renames the source HR to the destination database of HRTest.

    Both the methods to restore the database to SQL Server is efficient and useful. You can either run the T-SQL command and go to SQL Server Management Studio to follow the interface method. But these methods are not error-free, and you can face several issues like the following:

    • Invalid file format.
    • Invalid source file path.
    • Inadequate SQL permissions to run the T-SQL command.
    • Incorrect name of MDF and LDF files.
    • Insufficient memory space to restore the database.

    Also, improper restoration can delete or corrupt the data. After reading these issues, you can easily understand that a wrong step can not only stop the restoration but further corrupt the database. A failed restoration can give different error messages based on the situation like:

    Restore failed for Server ‘ServerName’.
    Exclusive access could not be obtained because the database is in use.

    To avoid such circumstances, use the Kernel SQL Backup Recovery software which can restore even SQL database (MDF/LDF) files even from corrupt backup files.

    Conclusion

    Restoration of SQL database is a simple procedure; if you follow all the crucial steps and fulfill the prerequisites, then everything is okay. But, a wrong step can corrupt the database and make complete data inaccessible. That’s why you should take Kernel SQL Backup Recovery tool which can restore SQL data even from corrupt backup files.