6

I took a backup of a database from 1st server (which runs SQL Server 2014).

When I try to restore the backup to a 2nd server (which runs SQL Server 2012) I get this warning message:

Warning message - a tail-log backup of the source database will be taken. View setting on the Options page.

If I ignore this message and try to restore, it fails.

Any idea ? What should I do ?

5
  • 3
    There is no SQL Server 2013 - we have 2005, 2008, 2008 R2, 2012, 2014 and soonish 2016 - but no 2013. So which one are you really using?
    – marc_s
    May 23 '16 at 10:38
  • What error is being returned when it fails? You may need to look at the SQL error log. May 23 '16 at 14:01
  • Plz check, I have corrected the SQL Version. May 24 '16 at 5:52
  • In the future, make sure you are logged in when you are editing your question – that way your changes will apply immediately instead of having to go through the peer review process.
    – Andriy M
    May 24 '16 at 6:17
  • You cannot restore 2014 backup to 2012.
    – SQLPRODDBA
    May 24 '16 at 6:17
10

Try to uncheck the option and restore.

enter image description here

0
6

TailLog backup contains the transaction log that was generated after the last log backup.so this is necessary if you want to restore to latest point in time.When we try to restore the database which is online and whose tail log has not been backed up ,SQL Server will throw the error you are getting

To avoid this error use NoRecovery.

But with above option,you will lose data forever. So I recommend,
1.Take the tail log backup too
2.Restore your main database with recovery
3.Then your tail log backup with no recovery

Below is some additional info on why you are getting error(highlighted Relevant info which applies to you).Taken From Books Online

We recommend that you take a tail-log backup in the following scenarios:

If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUPTransact-SQL statement.

If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional.

If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

On a damaged database backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes. If a tail-log backup cannot be created, any transactions committed after the latest log backup are lost.

2
  • The above statement "But with above option,you will lose data forever" regarding using NORECOVERY is not accurate. In fact this is what you need to use if you have transaction log backups that needed to restore after the full backup! The NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. Apr 8 '18 at 15:46
  • @RonenAriely let me check what I wrote,I will update this if needed.Thanks Apr 8 '18 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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