Sql-server – Extract mdf & ldf files from backup file

In brief: NO.

The transaction log file (the .ldf file) is a vital and absolutely necessary part of any SQL Server database. You cannot skip this, you cannot opt to not have it - just doesn't work. SQL Server must have a transaction log stored in a file - no way around that.

The backup contains two pieces of information:

• the database files layout and metadata, ie. RESTORE FILELISTONLY ...
• the actual data

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.