Sql-server – Backup too big to restore

Log shipping would also be a viable approach I think.

You could set it so that logs are shipped and restored overnight, but that gets suspended during business hours - or runs a lot less frequently. Since restoring new logs requires exclusive access to the database. So you may run the restores up until 8:30 AM, and then restore the next set at noon. This way their reports in the morning reflect up until 8:30, and in the afternoon they have caught up a little bit more.

The benefits over replication and/or your current approach?

1. Depending on the lag, this can also save you from a "shot yourself in the foot" scenario in production. If you delay the log shipping you may still be able to get at the "before mistake" data on the reporting server without having to go through the full process of restoring the production data and then working forward to get the most recent pre-mistake data.

2. Far less pounding of the server than shipping and restoring whole backups all at once nightly.

Log shipping isn't the panacea by any means, but it isn't difficult to set up, works in any edition (even though not all editions support a hokey wizard), and solves a couple of different problems.

Here are the steps to reproduce this, tested on SQL 2012 SP1 Developer Edition. This does not occur on SQL 2008. To summarize, a database created in SQL 2012 while the model database is in SIMPLE recovery, that has a full backup taken while an extra log file exists, cannot create usable differential backups if that extra log file is ever deleted.

ALTER DATABASE [model] SET RECOVERY SIMPLE
GO
CREATE DATABASE [DefunctTest]
GO
ALTER DATABASE [DefunctTest] ADD LOG FILE ( NAME = N'DefunctTest_log2', FILENAME = N'D:\DefunctTest_log2.ldf' , SIZE = 25600KB , FILEGROWTH = 10%)
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestPostLogFile.bak' WITH INIT
GO
ALTER DATABASE [DefunctTest]  REMOVE FILE [DefunctTest_log2]
GO

BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestFull.bak' WITH INIT
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestDiff.bak' WITH DIFFERENTIAL, INIT
GO
--Show that the backups only have the one log file.
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestFull.bak'
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestDiff.bak'
GO
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestFull.bak' WITH 
MOVE 'DefunctTest' TO 'D:\DefunctTest2.mdf',
MOVE 'DefunctTest_log' TO 'D:\DefunctTest2_log.ldf', REPLACE, NORECOVERY
GO
--This restore will have the error.
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestDiff.bak' WITH RECOVERY
GO

USE [DefunctTest2]
SELECT * FROM sys.database_files
GO

I submitted a Connect item for this bug here. The only way I've been able to remove this defunct file is to detach the database, and re-attach with ATTACH_REBUILD_LOG.

UPDATE: The bug that creates this scenario in my repro script seems to have been fixed by this KB: https://support.microsoft.com/en-us/kb/2830400. From the comments it appears an additional fix is available for SQL2012/2014, the scenarios seem very similar: https://support.microsoft.com/en-us/kb/3009576