I use SQL Server. There is a problem with my differential backups. I take full backup of my database at the end of the week and beside, i also take differential backup of the database daily. I have created a job which makes these backing up tasks automatically. After taking those full and diff bakcups, i restore them with a job schudelar every day & week. My full restore job task works successfully every week but when my other task,which is restoring daily differential backup, try to work, it fails with " The log or differential backup cannot be restored because no files are ready to rollforward. " error message.
I have two database servers. One of them is production server and the other one is reporting server. Reporting server contains the same database in the production db server. At the end of every week, I take full back up of the db in prodcution db server for reporting server. In the same way, every midnight, i also take differential back up of the db in prodcution server for reporting server. Next day,I restore the last diff back up to the db in the reporting server. I use visual cron tool for the process but it has failed with that error. I tried to restore manual but got same error message.
Here is my restoring commands.
EXEC [dbo].[DatabaseRestoreMany]
@Databases = 'DB_2004',
@BackupRoot = '\\BCKPSRVR\BKUP\',
@BackupTypes = 'DIFF',
@DataFileDirectory = 'D:\DBFILES\DB_2004\',
@LogFileDirectory = 'D:\DBFILES\DB_2004\',
@DirectoryPerDatabase = 'Y',
@RecoveryState = 'STANDBY',
@ReturnBackupList = 'N',
@ReturnTaskList = 'N',
@Execute = 'Y'
How can i solve this issue? Can you help me?
Thank you in advance.
You've got this error because no full backup was restored prior to your restore from differential backup.
Today is wednesday. I had restored the full back up of the db on sunday.
You restored your full backup with recovery. Your database became operational, it's online, and no more log or differential backup can be restored now.
In order to restore your differential backup you should first restore your full backup with norecovery, and only then you can restore your differential backup.
Please refer this BOL article to understand how differential backup can be restored: Restore a Differential Database Backup (SQL Server)
To restore a differential database backup
Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the full database backup that comes before the differential database backup. For more information, see How to: Restore a Full Backup.
Execute the RESTORE DATABASE statement to restore the differential database backup, specifying:
The name of the database to which the differential database backup is applied.
The backup device where the differential database backup is restored from.
The NORECOVERY clause if you have transaction log backups to apply after the differential database backup is restored. Otherwise, specify the RECOVERY clause.