restore DB from bak file

Winston TheFifth 106 Reputation points
2022-07-10T17:55:39.153+00:00

I have a database, DB1. I want to create a 2nd DB from the bak file from DB1 to compare the DB at that time to the current DB1. Is this possible? How? When I create a 2nd DB called DB2 and try to restore from the bak file from Db1 it fails.

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,484 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Dillon Silzer 57,636 Reputation points
    2022-07-10T18:33:07.233+00:00

    Have you tried following the guide below by Charanjeet Kaur? Pay attention closely to detail from Steps 7 and on.

    If you have any questions please ask. If this has helped you please mark as an answer.

    https://www.stellarinfo.com/article/restore-sql-database-with-a-different-name.php


  2. Erland Sommarskog 117.1K Reputation points MVP
    2022-07-10T19:33:42.11+00:00

    Use this command:

       RESTORE DATABASE DB2 FROM DISK = 'C:\temp\DB1.bak'  
       WITH MOVE 'DB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DB.ldf',  
           MOVE 'DB1_log' 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DB2.1df'  
    

    Replace C:\temp\DB1.bak with the actual path to the backup. In the strings that follow MOVE replace DB1 with your database name. Now, there is nothing that says that these names have to follow that pattern, but it is quite common that they do. You can use

       `  
       RESTORE FILELISTONLY FROM DISK = 'C:\temp\DB1.bak'  
    

    to find out what the actual names are.

    If you get any error messages, please share them with us.


  3. CathyJi-MSFT 22,346 Reputation points Microsoft Vendor
    2022-07-11T03:51:50.127+00:00

    Hi @Winston TheFifth ,

    If you using SSMS UI to restore DB1 backup to DB2, please note below key points. If it still failed, please share us the detail error message from SSMS UI by clicking the message beside red X in lower left corner, or get the detail error message from SQL server error log.
    219381-screenshot-2022-07-11-111551.jpg
    219382-screenshot-2022-07-11-112247.jpg
    219340-screenshot-2022-07-11-112341.jpg

    If you using T-SQL to complete this job, please refer to below T-SQL; Please change the location of .bak to the correct one in your environment.

    1.Determine the logical file names of the database, from the backup file, along with their physical paths by executing the RESTORE FILELISTONLY command:

    use DB1  
    RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\DB1.bak'  
    

    219363-screenshot-2022-07-11-114305.jpg

    check physical path of DB2

    use DB2  
    
    SELECT  
      name 'Logical Name',   
      physical_name 'File Location'  
    FROM sys.database_files  
    

    219326-screenshot-2022-07-11-120113.jpg

    2.Restore database.

    use master  
    
    RESTORE DATABASE DB2 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\DB1.bak'  
    WITH REPLACE, RECOVERY,  
    MOVE N'DB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB2.mdf',  
    MOVE N'DB1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB2.ldf';  
    

    219391-screenshot-2022-07-11-114550.jpg

    If it still does not work, please share us the error message for analysis.


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

    0 comments No comments

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.