How to take offline backup in sql without interrupting Application connectivity

  • Hello All,

    My requirement is to to take full backup of the databases in below two production environments. We were doing Prod migrations. Before that we were planning to perform mock cutover. Without any down time or stop the application we would like to take the backups in both environments.

    1st environment: Always on Configuration. Db size 2.7 TB. Two nodes. Backup preference for Automated backups are configured in AG is Primary.

    2nd environment: Active Passive Cluster with 2 nodes. Db size is 200GB

    Kindly suggest the best practice to take the backup in both the environments?

    In AG setup, Can I take copy only backup in primary server without any downtime? (Anyhow the users will connect to Listener. shall i proceed with copy only backup in secondary? Or do I need to follow any procedure to take the full backup without interrupting the application and user connectivity to db. )

    Is this works? Pls rectify if this is wrong.

    1.Drain roles for secondary node in Failover cluster.

    2.change backup pref to secondary in Ag.

    3.Take split full copy only backups in secondary instance as the db size is more than 2TB

    4.Once backup done start the 2nd node in Failover cluster.

    5.Change back to original setting to Primary for automated backups in AG.

    In Active-Passive Cluster environment how to take backup without interrupting the application? Is it ok to take copy only backup in Primary node?

    Do I need to take any precautions while taking the backups in both environments? If yes pls suggest.



    • This topic was modified 1 month, 1 week ago by  pols.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply