Follow Steps to Restore MS SQL Database with a Different Name

An SQL database Administrator has many job responsibilities related to the health of the SQL database. The SQL database’s data should remain accessible, quick, and safe. To maintain the properties of the database, the Administrator should be in regular practice to takes the backup of database files in a separate BAK file. It reduces the size of the database and makes it quicker to respond to the queries of users. Later, when the user requires any information from the backup file, then the administrator can restore the BAK file back to the SQL Server.

While taking a backup, there is no need to save the backup file at the same database, but you can use various methods for a flexible restore process. The first method is available within the SQL Server Management Studio software and the second method includes the process where administrator is required to run the Transaction-SQL commands for database restoration.

In both methods, the Administrator will get the option to rename the backup database and save it at the appropriate place.

Note: To restore a database under the full recovery model, administrator is required to backup the active transaction log too and for encrypted databases restore, the access permission for certificate or asymmetric key is must.

Restore the database using SQL Server Management Studio

1. Start SQL Server Management Studio, and connect with the SQL Server instance with proper authentication.
2. In the Object Explorer window, go to the respective database, right-click it and choose the option Restore database.
3. In the Restore Database window, you will see two options to select the source – Database and Device.
   • Database. If you choose the first option of the database, then it will show the list of all the databases that were backed up by the msdb backup technique.
   • Device. When you choose the Device option, then, click the Browse button to select the BAK file from its saving path location.
4. After selecting the backup database, go to the Destination section and rename the database with a new name that you want to assign to the restored database. You can also change the log file names along with.
5. Change the directory name of SQL Server folder to save the database file on it. It is recommended to avoid new database saving with the existing database folder for unnecessary confusions.
6. In the ‘Restore To’ option, choose the option ‘The last backup taken.’ You can also click the Timeline button and there will be a backup timeline of all the backups of the same database taken at different times. You can select anyone.
7. At the ‘Backup sets to restore grid’, choose the available backup files for the given location. Click OK.
8. Under the Restore options, there are three options to choose from –
   • Overwrite the existing database.
   • Preserve the replication settings.
   • Restrict access to the restored database.

   Select the first option “Overwrite the existing database” to continue restoring.

9. In the Recovery State drop-down, select ‘restore with recovery.’
10. In the Tail-Log backup, check the option ‘leave source database in the restoring state’.
11. Click OK and let the restore process begin.
12. After the completion of the restore process, you can get the message that the Database is restored successfully.
13. Find the database with the similar name under the Databases section.

Limitations of the method

1. The user who is restoring the database should be the only user who is accessing the database.
2. For the full recovery of the database, you should also restore the active transaction logs.
3. For the restoration of an encrypted database, the user must have the asymmetric or the certificate to encrypt the database.

T-SQL command to restore a database

It is relatively easier to restore the database using the T-SQL command with a new name as you can complete the job with a single command only.

Before using this command, you need to have the physical locations of the backup database file and the log file along with their logical names. Follow the below-given steps to find the logical names of the database files.

Step 1: Determine the logical file names of the database from the backup file and their physical paths by executing the RESTORE DATABASE command.

Step 2: After obtaining the logical and physical names of the database files, perform SQL restore to different database using RESTORE command with MOVE option. You can also take an example. In the following example you will restore data from JacobWork2016 database backup file to JacobWork_Data database along with .mdf and .ndf files.

Here is the command;

The command will restore the new database JacobWork from the JacobWork2016 backup along with the JacobWork_Data and JacobWork_Log files.

Limitations of the method

1. SQL permissions to run T-SQL command is not assigned
2. Not enough space available to restore database
3. Invalid file format, source file path
4. Technical skills are required
5. Incorrect database file names
6. Chances of Database inconsistency errors

Wrapping Up

The methods to restore the SQL database with a different name are quick using the SQL Server Management Studio and the T-SQL Commands but there are many prerequisites and settings that the user needs to fulfill. A single wrong step can not only stop the restoration process but also corrupt the database or the backup files also.

You can face several errors during the migration like the invalid source file path, invalid file format, incorrect names to the MDF and LDF files, not enough space for the restored database, or insufficient permissions to access or run the T-SQL command. One common error for the failed restoration of the SQL Server database is.

To overcome any crucial situation or save the backup data during the restoration, you should use any professional SQL Server recovery software that will take care of all the process and give you the desired results while securing your data.

Download Now