Extract mdf & ldf files from backup file?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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