[sql-server] Import .bak file to a database in SQL server

I have a file with .bak extension.

How can I import this date to a database in SQL Server?

How to restore a database from backup using SQL Server Management Studio 2019

If you have SQL Server Management Studio installed, you can restore database backup using its interface alone. Just follow the instructions:

1. Connect to your SQL Server and right-click on the “Databases” directory and choose “Restore Database”
for more info follow the below link
https://sqlbackupandftp.com/blog/restore-database-backup

1. Connect to a server you want to store your DB
2. Right-click Database
3. Click Restore
4. Choose the Device radio button under the source section
5. Click Add.
6. Navigate to the path where your .bak file is stored, select it and click OK
7. Enter the destination of your DB
8. Enter the name by which you want to store your DB
9. Click OK

Done

On Microsoft SQL Server Management Studio 2019:

On Restore Database window:

1. Choose Device

2. Choose Add and pick target file

3. OK to confirm

4. OK to confirm restore

1. Copy your backup .bak file in the following location of your pc : C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA
2. Connect to a server you want to store your DB
3. Right-click Database
4. Click Restore
5. Choose the Device radio button under the source section
6. Click Add.
7. Navigate to the path where your .bak file is stored, select it and click OK
8. Enter the destination of your DB
9. Enter the name by which you want to store your DB
10. Click OK

The above solutions missed out on where to keep your backup (.bak) file. This should do the trick. It worked for me.

You can use node package, if you often need to restore databases in development process.

Install:

npm install -g sql-bak-restore

Usage:

sql-bak-restore <bakPath> <dbName> <oldDbName> <owner>

Arguments:

• bakpath, relative or absolute path to file
• dbName, to which database to restore (!! database with this name will be deleted if exists !!)
• oldDbName, database name (if you don't know, specify something and run, you will see available databases after run.)
• owner, userName to make and give him db_owner privileges (password "1")

!! sqlcmd command line utility should be in your PATH variable.

https://github.com/vladimirbuskin/sql-bak-restore/

Simply use

sp_restoredb 'Your Database Name' ,'Location From you want to restore'

Example: sp_restoredb 'omDB','D:\abc.bak'

After opening and logging in to MS SQL Server Management studio

1. Right-click on Databases
2. Choose Restore Database
3. Choose Device and click on ...
4. Click Add and select the .bak file you want to import
5. Click OK

You're done!

Instead of choosing Restore Database..., select Restore Files and Filegroups...

Then enter a database name, select your .bak file path as the source, check the restore checkbox, and click Ok. If the .bak file is valid, it will work.

(The SQL Server restore option names are not intuitive for what should a very simple task.)

You can simply restore these database backup files using native SQL Server methods, or you can use ApexSQL Restore tool to quickly virtually attach the files and access them as fully restored databases.

Disclaimer: I work as a Product Support Engineer at ApexSQL

.bak files are database backups. You can restore the backup with the method below:

How to: Restore a Database Backup (SQL Server Management Studio)

This will show you a list of database files contained in DB.bak:

RESTORE FILELISTONLY 
FROM DISK = 'D:\3.0 Databases\DB.bak' 

You will need the logical names from that list for the MOVE operation in the second step:

RESTORE DATABASE YourDB
FROM DISK = 'D:\3.0 Databases\DB.bak' 

and you have to move appropriate mdf,ndf & ldf files using

With Move 'primarydatafilename' To 'D:\DB\data.mdf', 
Move 'secondarydatafile' To 'D:\DB\data1.ndf', 
Move 'logfilename' To 'D:\DB\log.ldf'

On SQL Server Management Studio

1. Right click Databases on left pane (Object Explorer)
2. Click Restore Database...
3. Choose Device, click ..., and add your .bak file
4. Click OK, then OK again

Done.

Although it is much easier to restore database using SSMS as stated in many answers. You can also restore Database using .bak with SQL server query, for example

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'D:\AdventureWorks2012.BAK'
GO

In above Query you need to keep in mind about .mdf/.ldf file location. You might get error

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\AdventureWorks.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended)

So you need to run Query as below

RESTORE FILELISTONLY 
FROM DISK = 'D:\AdventureWorks2012.BAK'

Once you will run above Query you will get location of mdf/ldf use it Restore database using query

USE MASTER
GO
RESTORE DATABASE DBASE 
FROM DISK = 'D:\AdventureWorks2012.BAK'
WITH 
MOVE 'DBASE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE.MDF',
MOVE 'DBASE_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE_1.LDF', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

Source:Restore database from .bak file in SQL server (With & without scripts)