none
A transactional log backup could not be generated because the database is in either NORECOVERY mode or STANDBY mode. Primary Database: RRS feed

  • Question

  • I have HA set up and working for a few years now.

    We want to log ship to a new server and use this to upgrade.

    We cannot take a outage for more than a few minutes and the database is over 500G in size, so a regular backup and restore won't work.

    I set up log shipping and get this error when trying to create the transaction log backup..........

    A transactional log backup could not be generated because the database is in either NORECOVERY mode or STANDBY mode. Primary Database: '

    There are a few databases on this server that are near static and are not part of HA.  I can set up log shipping on those with no issues, so I know the setup is correct.  I have done log shipping in the past with no issues.  This is the first time doing it with HA set up though.

    Source server is Windows 2008 R2 running sql server 2012.

    Destination server is Windows 2016 running sql server 2017.

    • Edited by DanB123789 Thursday, October 17, 2019 12:22 PM
    Thursday, October 17, 2019 12:02 PM

All replies


  • A transactional log backup could not be generated because the database is in either NORECOVERY mode or STANDBY mode. Primary Database: '

    As per the message it says the database of which you are trying to take log backup is either in recovery mode or standby mode which would not allow log backup. You can check the status from sys.databases

    select state from sys.databases where name='db_name'



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, October 18, 2019 6:53 AM
    Moderator
  • Hi DanB123789,

    Is the database for log shipping an availability group database?

    Could you tell me what is the backup preferences property of your primary replica in AlwaysOn Group?

    If so, can you view the status of the availability replica using the following command?

    select database_state_desc from sys.dm_hadr_database_replica_states  

    where database_id= DB_ID ( 'database_name' )  

    In addition, please refer to SQL Server AlwaysOn combined with Log Shipping (Automatic).

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 18, 2019 9:52 AM
  • It seems to me that there is a problem with backup preference in AlwaysOn. Try changing it to see if it works. Also, you may need to consider AlwaysOn failover scenario for log-shipping to work.
    If you explain how you set up your backup preference, folks might be able to see if anything is missed
    Saturday, October 19, 2019 4:48 AM
  • Yes it is an availability database.

    the query returns two rows.

    NULL and ONLINE.

    the backup preference is primary.
    • Edited by DanB123789 Monday, October 21, 2019 1:01 PM
    Monday, October 21, 2019 12:50 PM
  • The backup preference is for Primary.

    Shows both instances set at 50% priorities.

    Monday, October 21, 2019 12:56 PM
  • query above returns 0 for state
    Monday, October 21, 2019 1:41 PM
  • I run this......

    select sys.fn_hadr_backup_is_preferred_replica ('DRUG')

    on the primary it returns 0 and on the secondary it returns 0.

    I would have expected it to return a one on the primary.

    Monday, October 21, 2019 2:16 PM
  • Why are you using Log Shipping for this instead of async replicas in an AG? Why isn't the prod server in the AG with the 3 other DR servers? 

    https://developer.rackspace.com/blog/Microsoft-SQL-Server-log-shipping/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, October 22, 2019 4:52 AM
  • We are currently running on Windows 2012 and SQL Server 2012.

    The new server is going to be Windows 2016 and SQL Server 2017.

    Can you have different versions of OS and SQL Server in the same AG?

    Tuesday, October 22, 2019 11:14 AM