Sql-server – Restoring Differential Backups

sql serversql-server-2008

Is it possible to restore a differential backup separately?
The production server runs the following every sunday

DECLARE @Pathname VARCHAR(512) = 'N:\Backup\Dir\DBName_' + (select datename(dw,getdate())) + '.bak'
BACKUP DATABASE [DBName] TO DISK = @Pathname
WITH NOFORMAT, NOINIT,  NAME = 'Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

every other day I have the following as a differential backup

DECLARE @Pathname NVARCHAR(512)
SET @Pathname = 'N:\Backup\Dir\DBName_' + (select datename(dw,getdate())) + '.bak'
BACKUP DATABASE [DBName] TO  DISK = @Pathname
WITH  DIFFERENTIAL , NOFORMAT, INIT,  NAME = 'Diff Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Here is the report server restore job

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE

I keep getting the following error

The log or differential backup cannot be restored because no files are ready to rollforward.

Anyone help explain what i'm doing wrong?

UPDATE

I had to use WITH MOVE because the paths are different

Best Answer

In order to restore a differential backup, you need a database that is in "restoring" state. Which is to say that recovery can't have run yet. This is accomplished by adding the "norecovery" clause to your full backup restore. So, it goes a little something like this:

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE, NORECOVERY

--now you restore your differential backup
Related Question