Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
I don't have experience with differential backups, but what Recovery State is the restored database in after you restore from the full backup? You probably want to use RESTORE WITH NORECOVERY when you restore the database if you're going to try to add on a differential backup later. –  rsbarro Jan 24 at 21:31
    
Yes, the restore job needs to first restore the full (.bak) first with REPLACE, NORECOVERY. Then a second command to restore for the diff part (better name is .dak) shouldn't need REPLACE but should include RECOVERY. Alternately the RECOVERY part can be run as a 3rd separate step (restore database D with recovery) in case there are logs that need to be restored after the .dak. mslink –  crokusek Jan 24 at 21:43
    
So this is no way to restore a diff backup once the database leaves restoring state? –  Tsukasa Jan 25 at 2:36
    
No, once you bring the database online (WITH RECOVERY) you can't restore just the DIFFERENTIAL. You'll need to restore the full first then the differential. –  mrdenny Jan 26 at 7:01
    
Ok I figured it out, I have to use WITH MOVE because the paths are different. –  Tsukasa Feb 4 at 17:58
add comment

migrated from stackoverflow.com Jan 25 at 15:34

This question came from our site for professional and enthusiast programmers.

1 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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.