305

I have a file with .bak extension.

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

Improve this question
0

8 Answers 8

Reset to default
371

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

If this fails, you probably need to erase an existing database.
This isn't working for me. I point to the correct folder and it doesn't even see the files. I paste the exact file name on the dialog, and it says the file doesn't exist. Also, the dialog sees a drive that does not exist on my server. Does the database remember the location of last backup and will it only accept a restore from that point? I find it very odd that I can not select a backup file to restore.
If you're looking at a remote server, then you're seeing that server's file system and not your local machine.
This was the case for me. I was running SQL server on a docker container. So SSMS was reading from the file system of the docker container and not my localhost! So, consider copying the files from your host to the container.
My .BAK file was in the "Downloads" directory which existed underneath my user account. I had to move the .BAK file to the root of the C: in order for this tool to see it and allow me to restore from it. Files underneath my user just weren't seen for some reason.
This does not work. I right-click "Databases" but there is no "Restore Database...".
This doesn't work for me: I am getting the error message "The backup set holds a backup of a database other than the existing 'XYZ' database." Well, obviously. I just created the (still entirely empty) XYZ (stand-in name) database to receive the back-upped data.
|
51

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'
Improve this answer

1 Comment

This was very helpful for me as I wanted to restore a .bak file onto an sql Docker container with a macOS host using Azure Data Studio. All I had to do was copy the .bak to the container, adjust the path and change to /, and I was able to restore successfully.
25

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.)

Improve this answer

2 Comments

This is good advice, I did this and it reported that the restore was successful. But a bit of a problem still remains... the database is not listed in the object explorer. I try "attach", and it even has the correct .mdf file available (I presume a product of the restore). If I attempt the attach, it throws an error that it is already in use. Do you have any tips for this stage?
Be sure to do a refresh at the end to see your new database. This may be the quickest solution to getting up and running.
24

On Microsoft SQL Server Management Studio 2019:

enter image description here

On Restore Database window:

  1. Choose Device

  2. Choose Add and pick target file

  3. OK to confirm

  4. OK to confirm restore

enter image description here

Improve this answer

Comments

6
  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

Improve this answer

Comments

4

Simply use

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

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

Improve this answer

2 Comments

what's the difference between this and RESTORE DATABASE as in msdn.microsoft.com/en-us/library/ms178099(v=sql.105).aspx ?
Restore database perform restore operation only if structure of your DB same as backup file, but sp_restoredb perform restore even your database having different structure or totally empty(new).
4

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)

Improve this answer

Comments

-1

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/

Improve this answer

1 Comment

This is like buying a car to drive to your neighbor's house next door. Sure, you can do it, but it's way more steps that is necessary. Please also note you need to explicitly disclose any affiliation with tools or utilities you recommend.