287

I have a file with .bak extension.

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

Fernando Flores
  • 43
  • 1
  • 6
  • 1
    You can create an empty database, and restore the data to the empty dataBase, using your .bak – Michel Ayres Jul 24 '12 at 17:40
  • 1
    I am assuming you're using MSSQL, here is a [thread](http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/aa3a06ea-5422-43f4-ba7b-eee1405e9be7) explaining a few ways you can restore. – Andrew Keith Oct 08 '09 at 06:28
  • The esiest way is the 3rd answer, Right-Click databases, import, device, select .bak file, finished. – Mafii Aug 18 '16 at 10:00
  • Can someone expand the answer for an automation friendly solution? Going through UI and doing several clicks is not very efficient. – L. Holanda Sep 12 '17 at 18:05
  • When with Microsoft, without SO I am nothing! Many thanks. Upvotes talks a lot about Microsoft products. Last four hours I am installing MS SQL Server and importing DB. It is my client's requirement. For my use I shifted to MySQL decades ago. – Atul Nov 28 '22 at 18:15

9 Answers9

342

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.

Marcelo Mason
  • 6,750
  • 2
  • 34
  • 43
  • 6
    This works like a charm. These are excellent simplified instructions, and they're essentially the same instructions from the official Microsoft link noted in the most voted up answer here. – Leonardo Lopez Jun 04 '15 at 15:50
  • 3
    If this fails, you probably need to erase an existing database. – DigitalDesignDj Nov 16 '15 at 21:23
  • 4
    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. – James Nov 21 '16 at 18:49
  • 27
    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. – jeremysawesome Feb 07 '17 at 17:19
  • 6
    This does not work. I right-click "Databases" but there is no "Restore Database...". – Sam Hobbs Apr 29 '17 at 06:43
  • 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. – O. R. Mapper Oct 24 '18 at 13:43
  • ***VERY IMPORTANT!!*** make sure you have exclusive access to the db before you try to restore otherwise it will likely fail. Depending on your situation you might have to do different things, if it's just your local db you can tick 'close existing connections to destination database' on the options tab in the restore dialog. – matao Oct 26 '18 at 08:58
  • Works fine on SQL Server CTP 2019 – Mohammad Heydari Apr 17 '19 at 09:31
  • Should a restore from a bak take hours? The db size is around 300 gig. – DeadlyChambers Jan 28 '20 at 05:11
46

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'
mit
  • 11,083
  • 11
  • 50
  • 74
RameshVel
  • 64,778
  • 30
  • 169
  • 213
  • 3
    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. – andrewb Jun 05 '19 at 07:40
  • Glad it helped @andrewb :) – RameshVel Jun 06 '19 at 03:11
29

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

Ivan Stankovic
  • 1,602
  • 18
  • 13
24

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

James Lawruk
  • 30,112
  • 19
  • 130
  • 137
  • 1
    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? – AlanSE Feb 26 '15 at 04:00
  • 1
    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. – demongolem Jan 07 '16 at 22:17
  • 2
    This is the option that worked for me when wanting to import the same database .bak as a new database locally – Gurnzbot Jun 14 '19 at 18:25
20

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

Weky
  • 670
  • 8
  • 6
7
  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

John
  • 142
  • 1
  • 1
  • 16
Yash Saraiya
  • 1,035
  • 1
  • 20
  • 40
5

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)

Vikas Lalwani
  • 1,041
  • 18
  • 29
  • SqlServer 2019 gives this error msg: "RESTORE DATABASE' is not supported in this version of SQL Server" – zameb Jan 04 '22 at 16:13
  • @zameb can you give more details which SQL server .bak file you are using and where you are restoring this? – Vikas Lalwani Jan 05 '22 at 07:45
  • my bad Vikas, it happens in a AzureDB instance, changing to a on premises one, it works – zameb Jan 05 '22 at 14:04
4

Simply use

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

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

Omprakash tomar
  • 51
  • 6
  • what's the difference between this and `RESTORE DATABASE` as in https://msdn.microsoft.com/en-us/library/ms178099(v=sql.105).aspx ? – unhammer Jun 21 '17 at 08:49
  • 1
    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). – Omprakash tomar Nov 10 '17 at 06:45
0

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/

Vladimir Buskin
  • 614
  • 4
  • 8