0

I have setup Automated backup in SQL Server 2012 Enterprise Database backup through 'Maintenance Plan'. I have also configure AlwaysOn High Availability to that server .There is 5 databases in the server, where there is 3 databases has added "Availability Database" in Availability Groups and properly configured with 'secondary replica' & where 'Availability Mode' is 'Synchronous commit' between both server replica.

The 'Maintenance Plan' backup is configure with 'copy-only backup' . Because i have added 3 databases in 'Availability Database' in availability group and which 'backup type' is 'full'. I am also attaching the screen shot here enter image description here

Whenever i am not selecting 'copy-only backup' in 'Back Up Database Task' window , as per microsoft alert message ' at promtly windows shows the message like ' This backup type is not supported on a secondary replica and task will fail if the task runs on a secondary replica. For information , see http://go.microsoft.com/fwlink/?Linkid=208213.

whenever i am selecting 'For availability database,ignore Replica Priority for Backup and Backup on Primary Settings'. The message shows like that 'This backup type is not supported on a secondary replica and task will fail if the task runs on a secondary replica. Note: Ignoring the backup priority and availability group settings may results in simultaneous parallel backups if the maintenance plan is cloned on all replicas. for more information, see: http://go.microsoft.com/fwlink/?Linkid=208213

So, I have finally select the option like 'copy-only Backup' due to copy-only backup in 'secondary replica' .

Note:- The 'Availability mode' of server is 'Synchronous commit' mode ( from 'Primary Replica Server' to 'Secondary Replica Server').

The schedule backup is running successfully , and 2 backup files are generating in (H: drive).Because out of 5 database (3 database are in full recovery mode, which are added in 'Availability Databases' and other 2 database are in 'simple recovery model'. Only 'Simple Recovery model' database backup file is generating. As microsoft BOL Here and MSSQLTIPS Blog documentation Here At the time of this writing, only transaction log backups and full backups with COPY_ONLY are supported on secondary replicas. But the'text file report' is not generating.

I am attaching the 'Log file viewer' history screen shot here enter image description here

I am also attaching the backup preferencesscreen window here. enter image description here

Note:- The Schedule Backup location drive is 'Network Drive'. In mine case here, it is (H:) Drive. As shown in picture.

I have configure the folder path of 'Reporting and logging' of my network drive. Here it is (H: Drive). I am attaching the screen shot of that window here enter image description here

My MS SQL Server 2012 Enterprise version is

Microsoft SQL Server 2012 - 11.0.2325.0 (X64) Jun 8 2012 18:54:43 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

-----------------After Edit ---------------

Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.2325 Report was generated on "Primary Replica instace ID".
Maintenance Plan: AlwaysOn Backup
Duration: 00:00:01
Status: Succeeded.
Details:
Back Up Database (Full) (Primary Replica instance ID)
Backup Database on Local server connection
Databases: Database1,Database2,Database3,Database4,Database5
Type: Full
Append existing
Task start: 2016-09-10T00:00:01.
Task end: 2016-09-10T00:00:03.
Success

Note:- Here i have edited my Server instance id as well as Database Name . Because this is my Live server database. Here only Database4 and Database5 file is generated after execution of schedule backup. Which time is showing like this in mail message. Which is

Task start: 2016-09-10T00:00:01.
Task end: 2016-09-10T00:00:03.
Success

Note:-I am not including TSQL script here.

My concern is that What i am committing the mistake here for 'text file report' generation in 'Primary Replica Server'. Any tips or suggestions will be appreciated.

  • @Sean Gallardy, Yes. Out of 5 Databases ,Only 2 database files are created in assigned folder. Which are not added in 'Availability databases' in 'AlwaysOn High Availability Group'.And rest three are 'Copy-only' backup in secondary replica , as per company policy. – Md Haidar Ali Khan Sep 21 '16 at 12:44
0

Maintenance plans are garbage so this is running exactly as designed. However you don't need to take copy only backups on the secondary, you can if you want to but there's little point.

Any errors here are spurious. If you don't like them then your options are to switch to something professional like Hallengren scripts or Minion Backup; or ignore the errors (while maintaining normal testing of your backups to ensure they're viable).

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.