Share via

How to restore .bak file to SQL?

Gabbard, Art 0 Reputation points
2024-06-19T14:28:34.9833333+00:00

I want to create a new SQL database and restore a .bak file to it from a different database.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,107 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rahul Randive 9,011 Reputation points Microsoft Employee
    2024-06-19T16:35:28.6266667+00:00

    Hi @Gabbard, Art

    To restore a .bak file to a new SQL database, you can use the SQL Server Management Studio (SSMS) or TSQL commands. Here are the steps to restore a .bak file to a new SQL database using SSMS:

    1. Open SSMS and connect to the SQL Server instance where you want to restore the .bak file.
    2. Right-click on the "Databases" folder and select "Restore Database".
    3. In the "General" section, select "Device" as the source and click on the "..." button to select the .bak file.
    4. In the "Options" section, select "Overwrite the existing database" if you want to replace an existing database with the same name. Otherwise, enter a new database name in the "To database" field.
    5. Click on the "OK" button to start the restore process.

    Alternatively, you can use TSQL commands to restore the .bak file to a new SQL database. Here's an example TSQL command:

    
USE [master]

RESTORE DATABASE [NewDatabaseName] FROM DISK = N'C:\Path\To\BackupFile.bak' WITH FILE = 1, MOVE N'LogicalDataName' TO N'C:\Path\To\DataFile.mdf', MOVE N'LogicalLogName' TO N'C:\Path\To\LogFile.ldf', NOUNLOAD, STATS = 5

    Replace "NewDatabaseName" with the name of the new database, "C:\Path\To\BackupFile.bak" with the path to the .bak file, "LogicalDataName" with the logical name of the data file, "C:\Path\To\DataFile.mdf" with the path to the data file, "LogicalLogName" with the logical name of the log file, and "C:\Path\To\LogFile.ldf" with the path to the log file.

    Reference document - https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver16&tabs=ssms

    Thank You!

    0 comments
  2. Erland Sommarskog 103.9K Reputation points MVP
    2024-06-19T21:13:06.8166667+00:00

    First run

    RESTORE HEADERONLY FROM DISK = 'C:\temp\DB.bak'

    This should preferably only return one row. If more than one row is returned, there are multiple backups in the file. I will for the rest assume that there is only one file.

    Next run:

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

    This will give you the logical names of the files in the database. In most cases there are two, one data file and one log file. These names are in the first column.

    Now you can run:

    RESTORE DATABASE YourDB FROM DISK = 'C:\temp\DB.bak'
WITH MOVE 'File' TO 'Path\YourDB.mdf,
     MOVE 'File_log' TO 'Path\YourDB.ldf'

    Here File and File_log are placeholders for the names you got from RESTORE HEADERONLY.

    Path is where you want to put the databases. When in doubt, run sp_helpdb master, and use that path.

    The name of the database is at your choice. As Dan says, it does not have to be the name of the original database.

    RESTORE DATABASE also enters the database into the system catalog. There is no need to create it first.

    0 comments
  3. ZoeHui-MSFT 34,581 Reputation points
    2024-06-20T02:10:06.9933333+00:00

    Hi @Gabbard, Art,

    You may take a reference to the official documentation.

    Backup and restore a SQL Server database with SSMS

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments