SQL Transaction Log Restore DB Different Name

rr-4098 2,026 Reputation points
2023-06-02T03:22:21.1233333+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,492 questions
0 comments No comments
{count} votes

Accepted answer
  1. RahulRandive 10,486 Reputation points Volunteer Moderator
    2023-06-02T12:42:21.58+00:00

    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.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator
    2023-06-02T07:53:49.7433333+00:00

    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.

    0 comments No comments

  2. LiHongMSFT-4306 31,566 Reputation points
    2023-06-02T08:47:39.1066667+00:00

    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.

    0 comments No comments

  3. rr-4098 2,026 Reputation points
    2023-06-02T11:03:25.8433333+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.