I have a backup procedure that involves making a full backup over the weekend and then making differential backups during the week. The reason to do this is that it takes 6 hours to complete the full backup and only a few minutes to make the differential backups.
I can restore the full backup via the Microsoft SQL server Management Studio or via the following script:
RESTORE DATABASE [WS_MODELL]
FROM DISK = N'C:\PRECCP\Database\WS_MODELL.BAK'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
When I try to restore the differential backup via the management studio or via the following script I get the following error:
RESTORE DATABASE [WS_MODELL] FROM DISK = N'C:\PRECCP\Database\WS_MODELL_D1.BAK' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)
After searching the internet I found
http://blog.sqlauthority.com/2007/09/02/sql-server-fix-error-msg-3117-level-16-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
"This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored."
Example of restoring differential backup successfully after restoring full backup.
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksFull.bak'
WITH NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksDiff.bak'
WITH RECOVERY;
Reference : Pinal Dave (http://www.SQLAuthority.com)
In my case this meant:
RESTORE DATABASE [WS_MODELL]
FROM DISK = 'C:\PRECCP\Database\WS_MODELL.BAK'
WITH NORECOVERY;
RESTORE DATABASE [WS_MODELL]
FROM DISK = 'C:\PRECCP\Database\WS_MODELL_D1.BAK'
WITH RECOVERY;
