Sql-server – Should I restore without the log file

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.

If you are saying that some databases are being skipped, then I would bet money that you are using sp_MSforeachdb. Stop using that procedure, it is an unreliable pile of unsupported and undocumented crap that is known to skip databases in certain scenarios (usually where > 100 databases are used on a busy system).

Solution: write your own cursor with sensible options:

CREATE PROCEDURE dbo.my_own_foreachdb
  @command NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @db SYSNAME, @sql NVARCHAR(MAX);

  DECLARE c CURSOR 
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR SELECT name FROM sys.databases;

  OPEN c;

  FETCH NEXT FROM c INTO @db;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'EXEC ' + QUOTENAME(@db) + '..sp_executesql @command';

    EXEC sp_executesql @sql, N'@command NVARCHAR(MAX)', @command;

    FETCH NEXT FROM c INTO @db;
  END

  CLOSE c;
  DEALLOCATE c;
END
GO

Sorry, untested, busy day over here - but should demonstrate the approach.

Or see this and this (I wrote a more reliable and flexible replacement).