Ygthkb

Taking an academic pseudonym?

Dot product with a constant

Why are "square law" devices important?

Why does this quiz question say that protons and electrons do not combine to form neutrons?

SQL Server 2017 crashes when backing up because filepath is wrong

Why is Bernie Sanders maximum accepted donation on actblue $5600?

Buying a "Used" Router

Why do we divide Permutations to get to Combinations?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Isn't a semicolon (';') needed after a function declaration in C++?

Why is Shelob considered evil?

Is there a way to pause a running process on Linux systems and resume later?

What if you do not believe in the project benefits?

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

Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one

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

What's the function of the word "ли" in the following contexts?

Is Screenshot Time-tracking Common?

Why is quixotic not Quixotic (a proper adjective)?

Did the characters in Moving Pictures not know about cameras like Twoflower's?

Now...where was I?

Is it possible to detect 100% of SQLi with a simple regex?

What does it mean when an external ID field follows a DML Statement?

Aligning Systems of Equations

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 20 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 20 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 20 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 20 mins ago

answered Feb 14 at 4:21

jadarnel27jadarnel27

5,46811937

share|improve this answer

edited 20 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