Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to extract SQL Server files (.mdf & .ldf) from a backup file (.bak) to a physical location, without restore database?

I need a restore query or script to restore files from bak file only, no attaching to server. Please don't speak about detaching query after restore.

share|improve this question
    
Not possible I'm afraid. If you can explain why you want to do this, we may have other suggestions that could help. – Mark Storey-Smith Jun 24 '12 at 10:35
    
I have many customers and sometimes they give me backup for fix some issue. I want to restore their backup as ms sql files and use them in local file without attaching to sql server (connection runtime attaching) – Hamid Jun 24 '12 at 10:55
6  
If you know how to 'use' an MDF w/o attaching it to a runtime SQL Server then I'm confident you know perfectly well how to extract an MDF from a BAK... – Remus Rusanu Jun 24 '12 at 11:55
    
When you use runtime attaching of a database, it still must be attached to an instance of SQL Server. Are you connecting to a remote instance of SQL? Are you working with a local instance of SQL Express? Are you using a local SQL Express User Instance? Do you have access to the source code for the utility to work with the MDF and LDF files? – nabrond Jun 24 '12 at 13:39
3  
Real men run queries in a hex editor! – Andomar Jun 24 '12 at 15:16

ApexSQL has recently released a tool that can do this. It's ApexSQL Restore. I haven't tested it fully yet, but you have to connect to a SQL Server instance to do this. It attaches database backup file and creates MDF and LDF files for the database.

share|improve this answer

RedGate software has a product called SQL Virtual Restore which will allow you to attach the backup file to an instance without restoring it and get out the information that you need. This is your only option without using a hex editor.

share|improve this answer

If you have a .bak file, and all you want is to restore that file to a mdf. Then this is what you can do. It's a little hacky but works.

First you need an empty .mdf and .ldf files. The easier way to do that is to create a Web Project in Visual Studio and then In the App_Data folder right click and add new database. That will create an mdf and a .ldf.

Now copy those files to the data directory of your SQLSERVER Express. If it usually under the C:\Program Files\Microsoft SQL Server. In my case it is in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

So if your database is PhoneBook just copy:

copy PhoneBook.mdf “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook.mdf” copy PhoneBook_log.ldf “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook_log.ldf”

Now attach your database:

open a Visual Studio 2008 Command Prompt or other prompt (Remember to right click the Run as Administrator, specially if it is Vista)

in the command prompt run:

sqlcmd –S ./SQLEXPRESS

and run the following commands:

CREATE DATABASE Phonebook ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook_log.ldf' ) FOR ATTACH ; GO

copy your .BAK file to the data directory.

copy PhoneBook.bak “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook.bak” NOTE: Why the data directory: It seems that in some installation you can have permission issues

RESTORE DATABASE PhoneBook FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK' WITH MOVE 'Phonebook' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook.mdf', MOVE 'Phonebook_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook_log.ldf', REPLACE

After that if everything runs ok then you can detach your database:

1> exec sp_detach_db 'Phonebook'

Now you can copy your mdf files back to where you wanted them

NOTE: If you don't know the name you have to put for Data and Log then Run commands like

`RESTORE HEADERONLY FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'

RESTORE FILELISTONLY FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'`

Hope this helps

share|improve this answer
3  
Hi there, Welcome to the site. I wonder how this answer solves the without restore database and no attaching to server. Please don't speak about detaching query after restore. parts? – Tom V Mar 30 at 11:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.