Read time 6 minutes
When you want to restore an 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 the 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 in the SQL Server.
- Connect to your current SQL server using SQL Server Management Studio.
- Under Object Explorer, go to Databases. Right-click and select the option Restore Database.
- Select the Device option and click the Browse button.
- Click the Add button to add the backup file (BAK) from its location.
- Click OK after selecting the BAK file.
- Click the OK button.
- 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 the restoration.
- Go to Options and check the option ‘Take tail-log backup before restore.’ Click OK.
- A successful message will show you that Database ‘HRTest’ was restored successfully.
This way you can restore a database with a different name to the SQL server even if the current database is present there.
Restore SQL database using Transaction-SQL method
You can also use a Transaction-SQL method to restore the backup file into a database with a different name.
- In the first step, restore the backup file by providing a logical or physical name
- In the second step, you can restore the whole database along with MDF and LDF files.
- Full Restoration using the NORECOVERY option.
WITH MOVE ‘PeterDatabase_Data’ TO ‘E:\SQLRestore\PeterDatabase_Data.mdf’
MOVE ‘PeterDatabase_Log’ TO ‘E:\SQLLog\PeterDatabase_Log.ldf’
WITH MOVE ‘PeterDatabase_Data’ to ‘E:\SQLDatabase\PeterDatabase_Data.mdf’,
MOVE ‘PeterDatabase_Log’ TO ‘E:\SQLLog\PeterDatabase_log.ldf’,
NORECOVERY
Then Run
Limitations of manual methods while restoring SQL database with a different name
Both methods to restore the database to SQL Server are 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.
- Inconsistency errors in the database.
- 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.
Use third-party tool for error-free restoration of SQL database with a different name
To avoid such circumstances, use the Kernel SQL Backup Recovery software, which can restore SQL database (MDF/LDF) files even from corrupt backup files easily after a quick fix, owing to the advanced algorithm used in developing the tool. It supports all SQL Server version files of all sizes, previews recovered database objects, and saves selective data to the Live SQL Server or the Batch file. The tool is handy and can be managed by all users.
Conclusion
Restoration of SQL database is a simple procedure either with the SQL Server Management Studio or Transaction-SQL scripts; 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 use the Kernel SQL Backup Recovery tool, which can restore SQL data even from corrupt backup files.
