6

Im working with SQL Server DB, what i need is that i have a DB1 and clients site communicate with this DB,Reporting module of the application is also using the same DB (i-e That shows different reports in web pages) client want to point the reporting instance to another DB i-e DB2.

What we need to do is: create a new DB2 i-e copy of DB1 and on daily basis we will take the differential backup of DB1 and will restore it to DB2.

For this i tried to take the differential backup of DB1 using this query,

BACKUP DATABASE [DB1] TO  DISK = N'D:\DB_location\DB1-Diff.bak' 
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'DB1-Differential Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

and to restore it to DB2 i use

RESTORE DATABASE [DB2] FROM  DISK = N'D:\DB_location\DB1-Diff.bak' 
WITH  FILE = 1,  norecovery,  NOUNLOAD,  STATS = 10
GO

it runs perfect for the 1st time but next time if gives me error:

Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please also advise if i can use Transaction Log backup if that is more batter then differential back up. Can any one help me to solve this issue.

Thanks in advance.

1
  • 1
    A DIFFERENTIAL backup is based on the last FULL backup. So if anything breaks that chain (and it can be Windows Backup that does so) then you are lost until the next FULL. However, LOG backups are not really connected to a FULL or a DIFFERENTIAL, but sort of exist in their own world. (Of course, the transactions have to connect from the current state of the restore.) So, if you have the correct log files saved you can restore them regardless of FULL or DIFF backups run in between.
    – RLF
    Jul 23, 2014 at 13:20

3 Answers 3

2

Depending on how up-to-date, you want the reports to be, you can use

  1. Log shipping : with delay of log restore on secondary and choosing the secondary to be read only with disconnect users when restoring the log. This will allow you to have read from secondary server.

    you just have to balance between the restore frequency on the secondary.

    log shipping will be much more efficient as compared to your approach and it works out of the box and you can even monitor it. Plus you get a warm standby server which will act as a DR if the primary goes down (cavet is -- depending on the frequency of your log restores, there will be some amount of dataloss)

  2. Transactional replication : if your main database is not undergoing schema changes and you want near-to-real time reporting, you can use T-rep. Just make sure that you are replicating ONLY objects that are required for your reports. The initial snapshot will have some penalty on your main database.

As a side note, depending on what version and edition of SQL server you are using, in 2012 and up, you can leverage AlwaysON with readable secondaries to offload reporting to the secondary server.

Let me know if you want any further clarification and I will be happy to help you out.

Edit:

it runs perfect for the 1st time but next time if gives me error:

The reason might be because the full backup's FirstLSN wont be matching the Differential backup's DifferentialBaseLSN ==> meaning if the values do not match, then you would need a full backup followed by a diff.

enter image description here

1
  • Full Backup (FirstLSN) <> Differential Backup (DatabaseBackupLSN), how i can solve this problem ??
    – Loai
    Feb 9, 2017 at 9:28
3

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.

Please have a look on the below blog.

http://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/

0

Answer is simple you did not restored a full backup first. Differential backup is only valid from latest full backup because it has information about extents that has changed after full backup has been done. So unless you take full backup and restore and then restore a differential backup it will keep on failing with the error you got.

1
  • @zzz A DIFFERENTIAL can be restored to a database that was last updated by a DIFFERENTIAL restore, when it is left in Restoring... mode. (Do this all the time in testing backups.) Since you are using NORECOVERY it leaves the database in Restoring... mode. Likely there is another full backup run during the intervening period. If so, then do as Shanky said and restore both the last full and the diff.
    – RLF
    Jul 23, 2014 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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