I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) appended with the previous month name. For example, on April 1st, I create a DB called "mydatabase_march2021" which is a copy of "mydatabase" current as of April 1st at 12:01am.

My current process involves removing the DB from the AG on the 3rd node promptly at 12:01am, renaming it, and then starting the slow process of backing up and restoring the 5TB DB to the 3rd node and adding it to the AG. Is there some other way I can do this without having to fully reinitialize the DB on the 3rd node? I need to have this copy available to business users by 5am.

I have tried removing the DB from the AG, offlining SQL, making a copy of the DB files, but there is no way to attach those files and keep the DB in recovering (which is necessary to reinitialize).

Additional Information:
Windows Server 2016 Standard
SQL Server 2012 SP4 Enterprise (will be upgrading to SQL 2016 or 2019 at some point in the next year)
Node 1 is in datacenter A
Node 2 and Node 3 are in datacenter B

  • 1
    Can you create a copy_only backup of the database on the 3rd node that you restore on that node with a different name? Then you don't have to remove the DB and add it back into to the AG.
    – Taryn
    Apr 8 '21 at 15:12
  • 1
    @Taryn. I could take a backup of the DB on the 3rd node, but I need that copy of the DB to only have data up to 12:01am. Is there a way to do that with a backups? Actually, now that I think about it, I could use a tranlog backup, and then roll it to 12:01am.
    – Jobs_Done
    Apr 8 '21 at 15:37

"I could use a tranlog backup, and then roll it to 12:01am"

Do that. You can restore the last full backup, then apply log backups with NORECOVERY in the days or hours before the first of the month, then apply the last log backup with STOPAT and RECOVERY or STANDBY.

Or you could create a Database Snapshot at midnight on the first of the month. That would avoid copying all 5TB at the beginning, and the snapshot will only store pages that change over the course of the month.

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.