SQL Transaction Log Restore DB Different Name

Question

We recently had an issue with a data in one of our DB's and had to restore a copy of it but rename it so we can view the current and restored DB's side by side. The problem is I need to restore each transaction log. If I change the destination name for the DB will it go to the restored DB name and not production? I have never tried a transaction log restore while changing the destination DB name.

Answer 1

Hi ,

Please find details below, how to restore backup using SSMS

Restore an earlier disk backup with a new database name where the original database still exists

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

Right-click Databases and select Restore Database...

On the General page, select Device under the Source section.

Select the browse (...) button to open the Select backup devices dialog box. Select Add and navigate to your backup. Select OK after you've selected your disk backup file(s).

Select OK to return to the General page.

1. In the Destination section, the Database box is automatically populated with the name of the database to be restored. To change the name of the database, enter the new name in the Database box.

Select Options in the Select a page pane.

1. Under the Tail-log backup section, uncheck "Take tail-log backup before restore".

reference document-

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver16#c--restore-an-earlier-disk-backup-with-a-new-database-name-where-the-original-database-still-exists

Hope this helps!

Let us know if you need any additional information.

Answer 2

If you say

RESTORE LOG YourCopyOfTheDB FROM DISK = '<path>' WITH NORECOVERY

The transaction log will be restored to the database you specify, not any other database.

Also, you cannot restore a transaction log to an active database. That is why I added NORECOVERY. Once you have restored WITH RECOVERY, you cannot apply any more transaction logs.

Answer 3

Hi @rr-4098

When you restore a transaction log backup in SQL Server by using SQL Server Management Studio, on the General page, in the Database list box, select the name of a database. Only databases in the restoring state are listed.

In the Select the transaction log backups to restore grid, select the backups to restore. This grid lists the transaction log backups available for the selected database.

Please refer to this doc for more details: Restore a Transaction Log Backup (SQL Server).

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thank you both for the feedback. I am using SSMS and not t-sal commands. Just to be clear, changing the destination name to the target db will force the log restore to go to that db only correct? Just need to make sure our prod db will not be impacted.