Restore SQL Server DB without transaction log

Question

Given a SQL Server 2008 .bak file, is there a way to restore the data file only from the .bak file, without the transaction log?

The reason I'm asking is that the transaction log file size of this database is huge - exceeding the disc space I have readily available.

I have no interest in the transaction log, and no interest in any uncompleted transactions. Normally I would simply shrink the log to zero, once I've restored the database. But that doesn't help when I have insufficient disc space to create the log in the first place.

What I need is a way to tell SQL Server to restore only the data from the .bak file, not the transaction log. Is there any way to do that?

Note that I have no control over the generation of the .bak file - it's from an external source. Shrinking the transaction log before generating the .bak file is not an option.

Answer 1

The transaction log is an integral part of the backup. You can't tell SQL Server to ignore the transaction log, because there is no way to let's say restore and shrink the transaction log file at the same time. However, you can take a look at DBA post to hack the process, although it is not recommended at all

Alternatively you could try some third party tools for restoring, particularly virtual restoring process that can save a lot of space and time. Check out ApexSQL Restore, RedGate Virtual Restore, Idera Virtual Database.

Disclaimer: I work for ApexSQL as support engineer