Sample Header Ad - 728x90

Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

0 votes
1 answer
367 views
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.
Asked by Md Haidar Ali Khan (6533 rep)
Sep 20, 2016, 11:51 AM
Last activity: May 18, 2025, 10:03 AM