Sfrgttk

How can changes in personality/values of a person who turned into a vampire be explained?

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

How can I differentiate duration vs starting time

What did Putin say about a US deep state in his state-of-the-nation speech; what has he said in the past?

Manager has noticed coworker's excessive breaks. Should I warn him?

Was Opportunity's last message to Earth "My battery is low and it's getting dark"?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

How do I write a maintainable, fast, compile-time bit-mask in C++?

In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

Is it Safe to Plug an Extension Cord Into a Power Strip?

80-bit collision resistence because of 80-bit x87 registers?

What is the name of this perspective and how is it constructed?

Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?

Found a major flaw in paper from home university – to which I would like to return

Now...where was I?

Why is Shelob considered evil?

What is the reason behind this musical reference to Pinocchio in the Close Encounters main theme?

Why are `&array` and `array` pointing to the same address?

What is formjacking?

How does the spell Slow affect freefall?

Why are "square law" devices important?

What does an unprocessed RAW file look like?

Dot product with a constant

log ship from older version to newer one

SQL Server: Can I log ship from SQL Server 2014 --> 2016?What is the undo pass that SQL Server does during a RESTORE WITH STANDBY operation?This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBYIssues with Log ShippingResuming log shipping, do I just restore the db over itself?Unexplained log ship slow downLog Shipping always fails, after a whileSQL Server 2016: cannot restore database from .bak file because of huge transaction log?SQL Server Backups suddenly change sizeMake a newer version of the LDF-file work with an older version of the MDF-fileLog shipping large database

3

As per below thread from SQL Server: Can I log ship from SQL Server 2014 --> 2016?

Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database

upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?

sql-server log-shipping

share|improve this question

edited Feb 14 at 4:16

safi qadri

asked Feb 14 at 4:00

safi qadrisafi qadri

213

add a comment | 

3

As per below thread from SQL Server: Can I log ship from SQL Server 2014 --> 2016?

Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database

upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?

sql-server log-shipping

share|improve this question

edited Feb 14 at 4:16

safi qadri

asked Feb 14 at 4:00

safi qadrisafi qadri

213

add a comment | 

As per below thread from SQL Server: Can I log ship from SQL Server 2014 --> 2016?

Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database

upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?

sql-server log-shipping

share|improve this question

edited Feb 14 at 4:16

safi qadri

asked Feb 14 at 4:00

safi qadrisafi qadri

213

Error: This backup cannot be restored using WITH STANDBY because a database

upgrade is needed. Reissue the RESTORE without WITH STANDBY.

share|improve this question

edited Feb 14 at 4:16

safi qadri

asked Feb 14 at 4:00

safi qadrisafi qadri

213

share|improve this question

edited Feb 14 at 4:16

safi qadri

asked Feb 14 at 4:00

safi qadrisafi qadri

213

asked Feb 14 at 4:00

safi qadrisafi qadri

213

asked Feb 14 at 4:00

safi qadrisafi qadri

213

1 Answer
1

active

oldest

votes

6

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL):

STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead. You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker. But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database. It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

17. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.

share|improve this answer

edited 17 mins ago

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We finally configured 2 test instances using NORECOVERY option with Log Shopping jobs – Backup, Copy and Restore setup to run every hour between following servers: 1. MS SQL Server 2008R2 as a source instance 2. MS SQL Server 2016 as a target instance – as we need our current 2008R2 databases to be upgraded/migrated to 2016 instance Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016
  
  – safi qadri
  Feb 18 at 10:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).
  
  – safi qadri
  Feb 18 at 10:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?
  
  – safi qadri
  Feb 18 at 10:02
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  "However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured." You can't use Windows/NTFS Timestamps as a valid mark of being used. It's not at all applicable for database files due to the way it works.
  
  – Sean Gallardy
  Feb 18 at 17:47
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229694%2flog-ship-from-older-version-to-newer-one%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

6

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL):

STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead. You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker. But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database. It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

17. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.

share|improve this answer

edited 17 mins ago

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We finally configured 2 test instances using NORECOVERY option with Log Shopping jobs – Backup, Copy and Restore setup to run every hour between following servers: 1. MS SQL Server 2008R2 as a source instance 2. MS SQL Server 2016 as a target instance – as we need our current 2008R2 databases to be upgraded/migrated to 2016 instance Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016
  
  – safi qadri
  Feb 18 at 10:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).
  
  – safi qadri
  Feb 18 at 10:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?
  
  – safi qadri
  Feb 18 at 10:02
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  "However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured." You can't use Windows/NTFS Timestamps as a valid mark of being used. It's not at all applicable for database files due to the way it works.
  
  – Sean Gallardy
  Feb 18 at 17:47
  

  
  
  
  

add a comment | 

6

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL):

STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead. You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker. But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database. It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

17. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.

share|improve this answer

edited 17 mins ago

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We finally configured 2 test instances using NORECOVERY option with Log Shopping jobs – Backup, Copy and Restore setup to run every hour between following servers: 1. MS SQL Server 2008R2 as a source instance 2. MS SQL Server 2016 as a target instance – as we need our current 2008R2 databases to be upgraded/migrated to 2016 instance Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016
  
  – safi qadri
  Feb 18 at 10:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).
  
  – safi qadri
  Feb 18 at 10:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?
  
  – safi qadri
  Feb 18 at 10:02
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  "However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured." You can't use Windows/NTFS Timestamps as a valid mark of being used. It's not at all applicable for database files due to the way it works.
  
  – Sean Gallardy
  Feb 18 at 17:47
  

  
  
  
  

add a comment | 

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL):

STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead. You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker. But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database. It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

17. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.

share|improve this answer

edited 17 mins ago

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

share|improve this answer

edited 17 mins ago

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937