locked
SQL Backup Nul RRS feed

  • Question

  • I have a 3 node always on sql setup where the database backups are moved to azure blob storage.

    so,Here is the scenario.since the primary server Databases are backed up we don't want the secondary server databases to be backed up and occupying the storage and cost. 

    Question:

    Do we have a way to eliminate the backups from writing into the disk? It should look like that backups are taken as shown in SQL Server logs but they should not be present anywhere.

    Any help on this would be much appreciated.

    Thanks,

    Wednesday, April 29, 2020 4:52 PM

Answers

  • Hi saravana raj,

    >Do we have a way to eliminate the backups from writing into the disk? 

    Yes.You can choose not to backup to this disk or delete the backup files in the disk.

    Try workaround as this: how-to-delete-old-database-backup-files-automatically-in-sql-server

    Actually, AlwaysOn allows the database administrator to set the replica on which he wants the backup to be performed. Its working principle is: AlwaysOn introduces a function sys.fn_hadr_backup_is_preferred_replica.By running this function in a TSQL script, you can determine whether the currently running replica is the replica you want to make a backup. You can create the same backup job on all replicas of the availability group, and these backup jobs use the same schedule to run.In the backup job script of each replica, you need to call the sys.fn_hadr_backup_is_preferred_replica function to determine whether the current replica is the preferred backup replica. If the instance running the script is the preferred copy for backup, the function returns 1; otherwise, the function returns 0. When the function returns "1", you can continue to run the backup job. The typical code segment of the backup job script is as follows:


    IF (NOT sys.fn_hadr_backup_is_preferred_replica (@DBNAME))
    BEGIN
    Select 'This is not the preferred replica, exiting with success';
    RETURN 0
    END
    BACKUP DATABASE @DBNAME TO DISK = <disk>
    WITH COPY_ONLY;

    Through this logic, even if multiple copies are running backup jobs at the same time, only one job will actually advance to the actual backup stage.The return value of the sys.fn_hadr_backup_is_preferred_replica function is determined by the settings on the Backup Preferences tab,(you can choose Primary for your case)

    Best Regards.

    yuxi


    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

    • Marked as answer by saravana raj Thursday, April 30, 2020 12:39 PM
    Thursday, April 30, 2020 3:05 AM

All replies

  • If you're taking a backup of the database on the primary, there is no need to take one on the secondary.

    If it's monitoring tools blowing up because it's saying that backups aren't being taken then the monitoring tool should be fixed to account for availability groups.

    I don't see an issue here other than potential failures in the monitoring tool or failure to configure the monitoring tool correctly. If it's about compliance, then the compliance audit and report should include the fact these databases are in an AG and give the backup information from the primary at that time.

    Leaning on MSDB backup history in a distributed environment across instances is not going to go well, as you can see. If nothing else, insert the backup dates, times, locations, etc., into a different tracking database which can itself be recovered. Do reporting or monitoring from that, for example.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Wednesday, April 29, 2020 5:31 PM
    Answerer
  • Would it not be possible to simply stop the backup on secondary, I also fail to understand what is point in taking multiple backups ?

    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

    Wednesday, April 29, 2020 5:31 PM
  • Hi saravana raj,

    >Do we have a way to eliminate the backups from writing into the disk? 

    Yes.You can choose not to backup to this disk or delete the backup files in the disk.

    Try workaround as this: how-to-delete-old-database-backup-files-automatically-in-sql-server

    Actually, AlwaysOn allows the database administrator to set the replica on which he wants the backup to be performed. Its working principle is: AlwaysOn introduces a function sys.fn_hadr_backup_is_preferred_replica.By running this function in a TSQL script, you can determine whether the currently running replica is the replica you want to make a backup. You can create the same backup job on all replicas of the availability group, and these backup jobs use the same schedule to run.In the backup job script of each replica, you need to call the sys.fn_hadr_backup_is_preferred_replica function to determine whether the current replica is the preferred backup replica. If the instance running the script is the preferred copy for backup, the function returns 1; otherwise, the function returns 0. When the function returns "1", you can continue to run the backup job. The typical code segment of the backup job script is as follows:


    IF (NOT sys.fn_hadr_backup_is_preferred_replica (@DBNAME))
    BEGIN
    Select 'This is not the preferred replica, exiting with success';
    RETURN 0
    END
    BACKUP DATABASE @DBNAME TO DISK = <disk>
    WITH COPY_ONLY;

    Through this logic, even if multiple copies are running backup jobs at the same time, only one job will actually advance to the actual backup stage.The return value of the sys.fn_hadr_backup_is_preferred_replica function is determined by the settings on the Backup Preferences tab,(you can choose Primary for your case)

    Best Regards.

    yuxi


    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

    • Marked as answer by saravana raj Thursday, April 30, 2020 12:39 PM
    Thursday, April 30, 2020 3:05 AM