How to restore only the data file and not the log file

Question

How to restore only the data file and not the log file

imSQrLy

Ten Centuries

Points: 1335
More actions
October 8, 2009 at 1:43 pm

#226990

I have a database backup that contains a mdf and an ldf. I want to only restore the MDF as the LDF file is trying to restore to 280GB. Is there a way to only restore the MDF file from the backup without using 3rd party tools? If i can get only the MDF i could to a single file attach.
Jimmy
"I'm still learning the things i thought i knew!"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nope. A restore will always restore the data and log files and will restore them to the same sizes they were at the time of backup. You can elect not to restore data files (non-primary filegroup) but the log must restore.

Also note that attaching an mdf without the log doesn't always work. Only if the DB was shut down cleanly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 2

i think that you dont know how to properly manage log files. you should read this one:

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

What you can do is lower the log file size on the source system, if it's appropriate, and then take a backup with a smaller log file.

Or get more disk space.

imSQrLy Ten Centuries Points: 1335 More actions · Answer 4

Marcin Gol [SQL Server MVP] (10/10/2009)

i think that you dont know how to properly manage log files. you should read this one:

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx%5B/quote%5D

Please do not assume that i am responsible for these databases. These are client dbs and they deleted data. The rest of the details would amuse you but it would take to much effort to completely describe all the follies that took place to leave us with all data truncated from each table in their database and that we only have a backup just prior to the truncation that has a transaction log of 230GB. I am trying to help the client and do not have 230 GB available to me and nor do they.

The actual BAK file is only ~1GB. So there is only about 1GB of actual data and the log was backed up just before the full backup relatively speaking. So i was hoping to extract only the mdf from the bak.

It seems there is not a way.

Jimmy

"I'm still learning the things i thought i knew!"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

imSQrLy (10/10/2009)
I am trying to help the client and do not have 230 GB available to me and nor do they.

Tell the client that if they can buy a 250GB hard drive (dirt cheap these days) then you'll be able to get their data back for them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

Or you should just buy a 500GB-1TB USB drive, attach it restore, then shrink things down, set a better log size and make a new backup.

It's not much $$, and you'll have a spare drive to carry around for situations like this.