4

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.

Omer
  • 43
  • 1
  • 1
  • 6
  • We need more information on how the tasks work, specifically which commands and programs they run. You wrote that the tasks are responsible to create the backups, yet the error message suggests that the issue is with restoring. – Etienne Ott Mar 13 '19 at 07:30
  • >>>but when my other task,**which is taking daily differential backup**, try to work, it fails with " The log or differential backup **cannot be restored**<<< So what your task is doing, backup or restore? You write it's taking backup, but the error says it tries to RESTORE – sepupic Mar 13 '19 at 07:32
  • 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. – Omer Mar 13 '19 at 09:07
  • What is "DatabaseRestoreMany"? What commands execute this proc? Do you try to restore your diff backup without prior restoring full backup? – sepupic Mar 13 '19 at 09:52
  • DatabaseRestoreMany is a store procedure that is written by someone else before. @sepupic Let me give you an example of the process. Today is wednesday. I had restored the full back up of the db on sunday. After that, i have been restoring my diff back up day by day. When my job try to restore the diff back up to reporting db, it has crashed and throw like that an exception. That's why, i restored the diff manual. Yesterday and today, i saw the same issue. – Omer Mar 13 '19 at 11:15

1 Answers1

5

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.

sepupic
  • 8,409
  • 1
  • 9
  • 20